Help - VBA Macro to Hide/Unhide Column For Spesific Sheets Only

[teddygantenggila]

Hi, all

I'm a newbie one at using VBA Macro to simplify my excel files. I admit that I use VBA by copying VBA macro after using VBA record.

However, I find some difficulties when trying to hide/unhide column using button, inserted from developer tab.

In my files, I have 9 sheets, it could be like below example:
- Sheet 1 - "Model Map"
- Sheet 2 - "Summary"
- Sheet 3 - "Pivot"
- Sheet 4 - "Data Input 1"
- Sheet 5 - "Data Input 2"
...
- Sheet 9 - "Data Input 6"

By clicking hide button I want to hide entire column of column B until L and column N until Y that contain Jan-Dec number. So by clicking this button, user can just see Full year summary at column M and column Z.

I try to use this VBA code for "hide button":

Sub ShowFY()

Dim s As Worksheet

For Each s In Sheets
If s.Name <> "Model Map" Or s.Name <> "Summary" Or s.Name <> "Pivot" Then
s.Range("B1:L1,N1:Y1,").EntireColumn.Hidden = True
Else
s.Range("B1:L1,N1:Y1,").EntireColumn.Hidden = False
End If
Next s

End Sub

And I use almost similar code for "unhide button"

Sub ShowDetail()

Dim s As Worksheet
For Each s In Sheets
If s.Name <> "Model Map" Or s.Name <> "Summary" Or s.Name <> "Pivot" Then
s.Range("B1:L1,N1:Y1,").EntireColumn.Hidden = False
Else
s.Range("B1:L1,N1:Y1,").EntireColumn.Hidden = True
End If
Next s

End Sub

At the beginning, I think this code works well, because if click hide, it hide column B-L and N-Y of Sheet 4 until 9, and vice versa for unhide button, this button can unhide those columns.

But the problem is here, when I choose unhide button to unhide column B-L and N-Y of Sheet 4 until 9, it also hide column B-L and N-Y of Sheet 1 until 3. Same with hide button, when I click hide button, it hide column B-L and N-Y of sheet 4 until 9, but also unhide column B-L and N-Y of sheet 1 until 3.

I try to find other code to make this button only hide/unhide column B-L and N-Y of sheet 4 until 9, without affecting any column of sheet 1 until 3. May I know the better code for this problem? Is it caused by "For each s in sheets" code?

Thank you very much for your help and support for my problem

Regards

 

[JasL]

Hi

The code below should help you. Just adapt to your data:

'------------------------------------------------------
Sub HideColumns()
'------------------------------------------------------
Call ApplyHideShow (True) 'Calls ApplyHideShow, and Hides columns on relevant sheets
End Sub

'------------------------------------------------------
Sub ShowColumns()
'------------------------------------------------------
Call ApplyHideShow (False) 'Calls ApplyHideShow, and Shows columns on relevant sheets
End Sub


'------------------------------------------------------
Sub ApplyHideShow(ToggleHideShow As Boolean)
'------------------------------------------------------
'Calls the HideShowColumns macro, passing two parameters, SheetName and HideShow (ie Hide or Show)
Call HideShowColumns("Sheet1", ToggleHideShow)
Call HideShowColumns("Sheet2", ToggleHideShow)
Call HideShowColumns("Sheet3", ToggleHideShow)
'etc
End Sub


'------------------------------------------------------
Sub HideShowColumns(SheetName As String, HideShow As Boolean)
'------------------------------------------------------
Sheets(SheetName).Select
Columns("B:D").EntireColumn.Hidden = HideShow
End Sub

 

[teddygantenggila]

Hi JasL

Nice to meet you
Thank you very much for your help. Very great, it works