VBA: Hide/Unhide Columns

[stefos]

How can i use a checkBox to hide the same column (eg. Col AH) from sheet2 to sheet10 and return on sheet1 at cell A1, using tick/untick option?
Can this work too with 5 checkboxes, each for hide/unhide one column, in previous condition?

I think this would be in a array with sheets, but dont know exactly what to do.

[Derk]

The attached workbook does what you ask. The code is attached to the first sheet and is activated when the checkbox is checked or not. The control is from the control toolbar. You can see the code by rightclicking on the sheet1 tab and slelecting View Code.

[stefos]

Dear Derk,
In attached workbook, your example works fine, but if every sheet's name are different, (e.g. sheet2=Jan, sheet3=Feb....sheet10=Dec, then how can i figure this out? tks again for your help.

[Dave Hawley]

Hi Stefos

Not sure what the code that Derk has given is, but the code below will loop through all Worksheets in the ActiveWorkbook

Sub ForEachSheet() 
    Dim wSheet As Worksheet 
    For Each wSheet In Worksheets 
        'YOUR CODE 
    Next wSheet 
End Sub 

[Andy Pope]

Stefos,

Here are two variations to Derk's example.
The first loop will change all worksheets apart from the active one.

The second loop compares sheet name with a list you supply.

Private Sub CheckBox1_Click() 
    Dim i As Integer 
    Application.ScreenUpdating = False 
     
    ' all sheets except current 
    For i = 1 To Worksheets.Count 
        If Worksheets(i).Name <> ActiveSheet.Name Then 
            Worksheets(i).Range("C1").EntireColumn.Hidden = CheckBox1.Value 
        End If 
    Next i 
    ' Or 
    ' all named sheets 
    For i = 1 To Worksheets.Count 
        Select Case Worksheets(i).Name 
        Case "Jan", "Feb"  ' add your sheet names here 
            Worksheets(i).Range("C1").EntireColumn.Hidden = CheckBox1.Value 
        End Select 
    Next i 
     
    On Error Resume Next 
    Application.ScreenUpdating = True 
    Cells(1, 1).Select 
End Sub 

Remember to comment out the loop you don't need.

Cheers
Andy

[stefos]

Hi Andy.
thanks . I've used the second option and worked fine.

PS:Dave thanks too