Activate/deactivate checkboxes using a macro, when those checkboxes have also macros assigned

Question

I have several checkboxes in my table, and each check box as a macro assigned to hide or unhide a column, depending on if it is checked or not. For example:&nbsp;Sub HideUnhideC()
    Range("C:C").EntireColumn.Hidden = _
        (ActiveSheet.Shapes("Check Box 1").ControlFormat.Value &lt;&gt; 1)
End Sub
Sub HideUnhideD()
    Range("D:D").EntireColumn.Hidden = _
        (ActiveSheet.Shapes("Check Box 2").ControlFormat.Value &lt;&gt; 1)
End Sub&nbsp;I would like to create a button, that activate and deactivates all the checkboxes at once. For this I have created a Command button with ActiveX ControlsThen I create a module and configure it so it activates and deactivates all the checkboxes&nbsp;&nbsp;Private Sub CommandButton1_Click()
For Each cb In Sheet1.CheckBoxes
cb.Value = xlOn
Next

End Sub

Private Sub CommandButton2_Click()
For Each cb In Sheet1.CheckBoxes
cb.Value = xlOff
Next

End Sub&nbsp;The buttons seem to work well and activate or deactivate the checkboxes, but it doesn't trigger the hiding/unhiding function.What am I doing wrong? I am pretty new with macrosThanks in advance

hansvogelaar · Accepted Answer

MrPet88&nbsp;
Use ActiveX check boxes instead of Form Control check boxes.
All code then goes into the worksheet module:
Private Sub CheckBox1_Click()
    Range("C:C").EntireColumn.Hidden = Not Me.CheckBox1
End Sub

Private Sub CheckBox2_Click()
    Range("D:D").EntireColumn.Hidden = Not Me.CheckBox2
End Sub

Private Sub CommandButton1_Click()
    Dim ctl As OLEObject
    For Each ctl In Me.OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            ctl.Object.Value = True
        End If
    Next ctl
End Sub

Private Sub CommandButton2_Click()
    Dim ctl As OLEObject
    For Each ctl In Me.OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            ctl.Object.Value = False
        End If
    Next ctl
End Sub

Forum Discussion

Activate/deactivate checkboxes using a macro, when those checkboxes have also macros assigned

Share

Resources