VBA - Check Box and multiple macros

[candacem]

Hi Everyone,

I have two macros to change formatting and formulas and want to assign them to a Forms Control check box. I want one macro to run when checked and the other when unchecked.

Thanks!

 

[L. Howard]

Give this a try.

To test it out, copy the three macros to a standard module, and assign Sub Macrn_Off_Click() to your check box (right click the ck box "Assign Macro")
Click the box to check ON and Box_Code_ON runs to simulate the formatting code for click ON.
Click the box to check OFF and Box_Code_OFF runs to simulate the formatting code for click OFF.

Howard

Sub Macro_On_Off_Click()
    
    With ActiveSheet.Shapes(Application.Caller)
       
        If .ControlFormat = xlOn Then
           Box_Code_ON
          Else
           Box_Code_OFF
        End If
        
    End With
    
End Sub

Sub Box_Code_ON()
MsgBox "Format code for box runs when clicked ON"
End Sub

Sub Box_Code_OFF()
MsgBox "Format code for box runs when clicked OFF"
End Sub

 

[Fluff]

Alternatively you could just use 1 macro

Sub ChkBx()

    Dim Chk As CheckBox

    Set Chk = ActiveSheet.CheckBoxes(Application.Caller)

    If Chk = 1 Then
        Range("A1").Interior.Color = vbRed
    Else
        Range("A1").Interior.Color = vbBlue
    End If
    

End Sub