Changing Value based upon if a button is clicked in Excel

August 15, 2022

I am trying to have a button change a value of a cell in Excel dependent on whether button is true of false. At the moment the button can be clicked and it will automatically go to false. If the button is clicked again it should go to true value but it just stays false. I’ve extensively researched this but have not come to any conclusions. Any help would be appreciated.

Private Sub CommandButton1_Click()
If CommandButton1.Value = True Then
Range("H9").Value = "5"
Else
Range("H9").Value = "0"
End If
End Sub

>Solution :

Toggle Switch Implementation

Private Sub CommandButton1_Click()

    If InStr(1, CommandButton1.Caption, "OFF") Then
        Range("A1").Value = 5
        CommandButton1.Caption = "CommandButton1" & vbNewLine & "Current State: ON"
    Else
        Range("A1").Value = 0
        CommandButton1.Caption = "CommandButton1" & vbNewLine & "Current State: OFF"
    End If

End Sub