Hide/unhide columns of multiple sheets using tickbox

[bakarken]

Hello

I have a tickbox in Sheet1 and I would like a VBA that when this is ticked, columns H:M in ALL of Sheet2, Sheet3, Sheet4 and Sheet5 will hide. Then if this box is unchecked, they will reveal themselves.

I've seen examples where a VBA can apply to a single sheet, but is there a way it can be applied to multiple sheets?

Thanks

 

[gallen]

Hello. This code does as you ask, assuming the control is a form control and not Active X:

Paste this code in to a new module and assign this macro to the Check box

Sub ShowHideColumns()
    'Assuming Checkbox 1 is a form control and named "Check Box 1"
    
    Dim b As Boolean
    
    'record if the control is ticked
    b = Sheet1.Shapes("Check Box 1").ControlFormat.Value = 1
    
    Sheet2.Range("H1:M1").EntireColumn.Hidden = b
    Sheet3.Range("H1:M1").EntireColumn.Hidden = b
    Sheet4.Range("H1:M1").EntireColumn.Hidden = b
    Sheet5.Range("H1:M1").EntireColumn.Hidden = b
    
End Sub

 

[bakarken]

Thank you for that gallen - should I be removing the 'Assuming line and 'record lines??
Sorry only have little experience using VBA's

 

[DanteAmor]

If you have an ActiveX checkbox, then in the programmer tab, select Mode design, right click on the checkbox and select to see code, in the panel paste the following code:

Private Sub CheckBox1_Click()
    shs = Array("sheet2", "sheet3", "sheet4", "sheet5")
    For i = LBound(shs) To UBound(shs)
        Sheets(shs(i)).Columns("H:M").EntireColumn.Hidden = CheckBox1
    Next
End Sub

When you click on checkbox automatically the coulums are hide / unhide on all the sheets

 

[gallen]

Thank you for that gallen - should I be removing the 'Assuming line and 'record lines??
Sorry only have little experience using VBA's

You can do. Those lines are just comments. And have no effect on the compiled code when it runs.

 

[bakarken]

Thank you both its done now, perfect!!!!

 

[DanteAmor]

You're welcome.

 

[bakarken]

Dante I've come across a slight issue - hopefully you can help?

So the VBA was working but I've now decided I wish to lock sheets 2-5, such as rows 1-7 of each cannot be selected, edited etc. (rows 1-7 are locked, rows 8 onwards are unlocked).

I realise that hiding/unhiding locked cells will not work, so I have UNPROTECTED H1:M7, as these are the columns which I want to hide/unhide.

This is still not working when ticking the active box.

Is there a way that it can work?

To summarise: I wish to lock sheets 2-5 on A1:G7 and N1:AP7, and i wish a tickbox VBA to hide/unhide H:M (which are not locked/protected)

Thanks

 

[DanteAmor]

Try with this. change "abc" with your password

Private Sub CheckBox1_Click()
    shs = Array("sheet2", "sheet3", "sheet4", "sheet5")
    For i = LBound(shs) To UBound(shs)
        Sheets(CStr(shs(i))).Unprotect "abc"
        Sheets(CStr(shs(i))).Columns("H:M").EntireColumn.Hidden = CheckBox1
        Sheets(CStr(shs(i))).Protect "abc"
    Next
End Sub

 

[bakarken]

It's very slow but does seem to work, thank you!