Macro to unhide month selected

[howard]

I have spreadsheet with a dropdown menu in Cell A2 on sheet "service". All the sheets containing the months from Oct to September are hidden, except the current month that is selected from the drop down menu. I also have the following code below that unhides the month selected

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col As Long
Dim c As Range

Application.ScreenUpdating = False

If Target.Address(0, 0) <> "A2" Then Exit Sub
Columns("B:M").Hidden = False
Set c = Range("B1:M1").Find(Target, LookIn:=xlValues, lookat:=xlPart)
Columns("B:M").Hidden = True

If Not c Is Nothing Then
c.EntireColumn.Hidden = False
Else
MsgBox Target & " not found", 48, "ERROR"
End If

Application.ScreenUpdating = True

End Sub

I also have sheets "Parts", "branch1" & "Branch2". When I select the month in scell A2 from the drop down menu on Sheet "Service" then I want unhide the months in the other sheets as well pertaining to the month selected. They also have drop down menus and currently I have to go to each sheet individually to select the month from the drop down menu. I would like to automate this process by sdelecting the month from drop down menu in A2 on Sheet "Service" and all tmonth selected will be unhidden on all the other sheets as well

Your assistance will be most appreciated

 

[Marcelo Branco]

Hi,

If your months (headers in B1:M1) are text, like Jan, Feb, Mar....Dec, maybe you can create a macro in a Standard Module like this
(try it on a test-workbook)

Sub HideUnhide(aMonth As String)
    Dim wk As Worksheet
    Dim c As Range
    
    Application.ScreenUpdating = True
    For Each wk In ActiveWorkbook.Worksheets
        With wk
            .Columns("B:M").Hidden = False
            Set c = .Range("B1:M1").Find(aMonth, LookIn:=xlValues, lookat:=xlPart)
            .Columns("B:M").Hidden = True
            c.EntireColumn.Hidden = False
        End With
    Next wk
    Application.ScreenUpdating = True
End Sub

I'm assuming that you dont have other sheets in addition to Service, Parts, Branch1 and Branch2.

If you have you can put in the macro above something like
If wk.Name = "Service" or wk.Name = "Parts".......Then

And in sheet Service

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long
    Dim c As Range
 
    Application.ScreenUpdating = False
 
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Columns("B:M").Hidden = False
    Set c = Range("B1:M1").Find(Target, LookIn:=xlValues, lookat:=xlPart)
    Columns("B:M").Hidden = True
 
    If Not c Is Nothing Then
        Call HideUnhide(Target.Text)
    Else
        MsgBox Target & " not found", 48, "ERROR"
    End If
    Application.ScreenUpdating = True
End Sub

HTH

M.

 

[howard]

Hi Marcello

Thanks for the help. I have 2 other sheets "Macro" and "Months" Which must not be affected as they do not contain month headers and there do not have hidden columns

When changing the month the folowing code is highlighted in the macro in the module

c.EntireColumn.Hidden = False

Your assistance will be most appreciated in resolving this

 

[Marcelo Branco]

Hi Howard,

Try this

Sub HideUnhide(aMonth As String)
    Dim wk As Worksheet
    Dim c As Range
    
    Application.ScreenUpdating = False
    For Each wk In ActiveWorkbook.Worksheets
        With wk
            If UCase(wk.Name) <> "MACRO" And UCase(wk.Name) <> "MONTHS" Then
                .Columns("B:M").Hidden = False
                Set c = .Range("B1:M1").Find(aMonth, LookIn:=xlValues, lookat:=xlPart)
                .Columns("B:M").Hidden = True
                c.EntireColumn.Hidden = False
            End If
        End With
    Next wk
    Application.ScreenUpdating = True
End Sub

There was also a typo in the previous version (twice Application.ScreenUpdating = True). Fixed in this version.

HTH

M.

 

[howard]

Hi Marcelo

Thanks for all the help. When clicking on the drop down menu to select the month, the folowing code is highlighted

c.EntireColumn.Hidden = False

I changed this to columns.EntireColumn.Hidden = False, and when selecting the month, all the columns containing the months on all the sheets are unhidden i.e columns B to M were unhiden. I selected May and only wanted the column containing May to be unhidden

It would be appreciated if you would amend the code. If need be send me a PM with you email & I could email you my workbook

 

[Marcelo Branco]

Hi Howard,

Please check if the headers in the relevant sheets are exactly the same - text with no leading or trailing spaces - and located, all, in range B1:M1.

I would suggest that you copy the headers from sheet Service and paste them in the others relevant sheets (Parts, Branch1, Branch2) to avoid possible text discrepancies.

I made several tests and the macros seems to work.

M.