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

summerp

New Member
Joined
Sep 23, 2019
Messages
2
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
You could also use a change event, which will trigger automatically whenever A5 is changed
Code:
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

New Member
Joined
Sep 23, 2019
Messages
2
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
         If Format(Cl.Value, "mmm") = Target.Value And Year(Cl.Value) = Range("A2") Then Cl.EntireColumn.Hidden = False
 

Watch MrExcel Video

Forum statistics

Threads
1,130,092
Messages
5,640,066
Members
417,125
Latest member
sfreind

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top