[SOLVED] ActiveX Command Button Caption changes when losing focus

ActiveX Command Button Caption changes when losing focus

Because of the inability to reliably resize a checkbox form control, I'm using some code to simulate a checkbox using a command button. I'm using ChrW(&H2714) to generate a checkmark as the caption text for the button.

The code works except for when I click any part of the worksheet subsequently, the checkmark turns into a question mark (?). I'm not even a halfway decent coder, so please let me know if I'm doing something out-of-pocket.

Here's my code:

Private Sub CommandButton1_Click()

CommandButton1.Caption = IIf(CommandButton1.Caption = "", ChrW(&H2714), "") 
CommandButton1.BackColor = IIf(CommandButton1.BackColor = RGB(255, 255, 255), RGB(0, 255, 255), RGB(255, 255, 255))

If CommandButton1.Caption = ChrW(&H2714) Then

ActiveWorksheet.Range("L20") = Now() & " -" & Environ$("username")
ActiveWorksheet.Range("A20:N21").Interior.ColorIndex = 15

Else

ActiveWorksheet.Range("L20") = ""
ActiveWorksheet.Range("A20:N21").Interior.ColorIndex = 2

End If

End Sub

Solution

You can use a font which has a "check" symbol.

This works for me:

Private Sub CommandButton1_Click()
    Dim checked As Boolean
    With CommandButton1 'caption font set to WebDings
        checked = Not .Caption = "a"     'toggle checked state
        .Caption = IIf(checked, "a", "")
        .BackColor = IIf(checked, RGB(0, 255, 255), RGB(255, 255, 255))
        With Me
            .Range("L20") = IIf(checked, Now() & " -" & Environ$("username"), "")
            .Range("A20:N21").Interior.ColorIndex = IIf(checked, 15, 2)
        End With
    End With
End Sub