VBA Hide Columns Based on Date

jbow11 · Mar 12th 2016

Hey!

I am very new to VBA, and macro coding. I have tried finding solutions for what I am attempting to do in excel.

I am currently using the 2016 Mac Version of Excel with the project I am working on, so compatibility across platformed versions of Excel is a highly desired goal with this coding project.

My Current Project is attempting to use a form button to toggle which columns are active (Unhidden) and those which are inactive (hidden). I already was able to create another button for the spreadsheet which toggles all columns to unhidden state, however I am having difficulty with creating the necessary VBA code for my other button.

The Criteria is, the page automatically determines the current month by using the =month(today()) formula to display both text (cell B3) and the serial number of the month (cell B2).
This information is important as it is the filter for the button toggle. The goal is to create a button which will hide all columns displaying months which are not the current month.

The Column Ranges I have are Jan(C:C);Feb(D:D);Mar(E:E);Apr(F:F);May(G:G);Jun(H:H);Jul(I:I);Aug(J:J);Sept(K:K);Oct(L:L);Nov(M:M);Dec(N:N).

**Trebor76** · Mar 13th 2016

Re: VBA Hide Columns Based on Date

Hi jbow11,

Welcome to OzGrid!!

See how this codes goes:

Code

Option Explicit


Sub Macro1()


    Dim intMyMonthNum As Integer
    Dim lngMyCol As Long
    
    Application.ScreenUpdating = False


    'Returns the column number (i.e. 5 for Col. E) based on the date entry in cell B3 of the current sheet.
    intMyMonthNum = Evaluate("VLOOKUP(TEXT(B3,""mmm""),{""Jan"",3;""Feb"",4;""Mar"",5;""Apr"",6;""May"",7;""Jun"",8;""Jul"",9;""Aug"",10;""Sep"",11;""Oct"",12;""Nov"",13;""Dec"",14},2,0)")
    
    'Working backwards from Col. N (14) to Col. C (3) hide any column that doesn't equal 'intMyMonth'.
    For lngMyCol = 14 To 3 Step -1
        If lngMyCol = intMyMonthNum Then
            Columns(lngMyCol).EntireColumn.Hidden = False
        Else
            Columns(lngMyCol).EntireColumn.Hidden = True
        End If
    Next lngMyCol
    
    Application.ScreenUpdating = True


End Sub

Display More

Regards,

Robert

jbow11 · Mar 14th 2016

Re: VBA Hide Columns Based on Date

How exactly would I input the VBA code properly?
I opened the VBA editor panel, and attempted to put it under the Class Module opened by excel when I created the button.
[ATTACH=CONFIG]68567[/ATTACH]
This is what my spreadsheet currently looks like:
[ATTACH=CONFIG]68568[/ATTACH]
I'm sorry, I'm just really new to VBA (and to be brutally honest its been a few years since I've really done any real programming).

**Trebor76** · Mar 14th 2016

Re: VBA Hide Columns Based on Date

I think my code didn't post quite correctly - see how it goes now.

If you still have issues please post your workbook (devoid of any sensitive data) as screen shots don't help and are virtually impossible to read!!

Thanks,

Robert

jbow11 · Mar 14th 2016

Re: VBA Hide Columns Based on Date

Here is the spreadsheet, I wasnt able to make the macro run.
[ATTACH=CONFIG]68569[/ATTACH]

**Trebor76** · Mar 14th 2016

Re: VBA Hide Columns Based on Date

So much easier having the workbook!! Attached is right to go.

jbow11 · Mar 14th 2016

Re: VBA Hide Columns Based on Date

I noticed when deleted my macro before, I accidentally removed the macro for unhiding the hidden columns (just in case I wanted to compare previous months). Would you happen to know some VBA for opening up just those columns?

Thank you very much for your help so far! It works exceptionally.

**Trebor76** · Mar 14th 2016

Re: VBA Hide Columns Based on Date

You can unhide columns with a simple one liner like so:

Code

Sub UnhideAll()
    
    'Unhide columns C to N (inclusive)
    Range("C1:N1").EntireColumn.Hidden = False
     
End Sub

Robert

jbow11 · Mar 14th 2016

Re: VBA Hide Columns Based on Date

Thank you so much Robert! I really appreciate all your help!

**Trebor76** · Mar 14th 2016

Re: VBA Hide Columns Based on Date

You're welcome - I'm glad OzGrid was able to help

jbow11 · Mar 14th 2016

Re: VBA Hide Columns Based on Date

Ahh I see where my error was when I had tried to add your code. I was adding your code after the Sub CurrentMonth_Click () line.

VBA Hide Columns Based on Date

Participate now!

Share

Users Viewing This Thread