VBA to hide date columns based on user selection in drop-down list

[summerp]

First post and desperate to stop wasting my time trying to piece together bits of what I need from different searches. I am pretty new to writing code and will admit I don't always understand every line of code I find when searching.

I have a timesheet template with date values (properly formatted to serial #) in column range F8:NG8 and drop-down list to select "view" month of "Jan", "Feb", "Mar", etc. in cell A5.

When user selects "Jan" from drop-down, I want all columns in range F8:NG8 with date NOT in January to be hidden and likewise for each month selected.

I know this isn't super complicated yet I can't seem to get the code right. I have had success with the following code assigned to buttons to show current day/week/month (included month code below) which most users will use but just in case they want to view a one month period that is NOT the current month I have the drop-down selection option. How do I adjust this with "IF" arguments dependent upon drop-down? I also welcome any other more efficient suggestions.

Sub ShowMonth()
Dim MyRange As Range, c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyRange = Range("F$8:NG$8")

MyRange.EntireColumn.Hidden = True
For Each c In MyRange
If Month(c.Value) = Month(Date) Then
c.EntireColumn.Hidden = False
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub ShowMonth()
Dim MyRange As Range, c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyRange = Range("F$8:NG$8")

MyRange.EntireColumn.Hidden = True
For Each c In MyRange
   If Format(c.Value, "mmm") = Range("A5") Then
      c.EntireColumn.Hidden = False
   End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

You could also use a change event, which will trigger automatically whenever A5 is changed

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A5" Then
      Range("F$8:NG$8").EntireColumn.Hidden = True
      For Each Cl In Range("F$8:NG$8")
         If Format(Cl.Value, "mmm") = Target.Value Then Cl.EntireColumn.Hidden = False
      Next Cl
   End If
End Sub

This code need to go in the relevant Sheet Module

 

[summerp]

Thank you so much! I went with the change event code. Now my only issue is that the column range accounts for leap years.

I have cell A2 (named rCalYear) where user inputs the year. So in 2019 there is a column at the end of range that shows Jan 1/2020 to allow for other years when there is a Feb29. How would I adjust the code so if the date in range also does not match the year in A2 it won't show up in the "Jan" selection?

 

[Fluff]

How about

         If Format(Cl.Value, "mmm") = Target.Value And Year(Cl.Value) = Range("A2") Then Cl.EntireColumn.Hidden = False