Excel VBA Hallucinations With Forms Checkbox

This makes no sense. Win11, Excel 2021 64 bit. Create a new workbook, and add just one Check Box from the Forms Control menu.

Create two macros:

Sub Test()

    Dim Fink As Integer
    Fink = 3
    If Checkbox1 = 0 Then Fink = 0
    If ActiveSheet.CheckBoxes("Check Box 1") = 1 Then Fink = 1
    Range("A1").Value = Fink

End Sub


Sub Testy()

    Dim Fink As Integer
    Fink = 3
    If Checkbox1 = 1 Then Fink = 1
    If ActiveSheet.CheckBoxes("Check Box 1") = 0 Then Fink = 0
    Range("A1").Value = Fink

End Sub

Run the "Test" macro. As expected, Fink = 0 if unchecked, and Fink = 1 if checked.

Run the "Testy" macro. Regardless if checked or not, Fink = 3

I don't want to use ActiveX because the spreadsheet project I'm working on may go public, and some of the public fears ActiveX from strangers. The problems I'm having boil down to this simple example. I need to use the checkbox value in an If-Then statement, and banking on the "other state" as a failure state isn't comforting. Any ideas what's happening?

Solution

I think you should treat Excel with a bit more respect, as much as I dislike it from time to time, it's not fully AI yet so it tends to do exactly what you tell it do it.

In this case the problem lies with your statement ActiveSheet.CheckBoxes("Check Box 1"), which is not correct usage. The correct syntax for checking the state of checkboxes is ActiveSheet.CheckBoxes("Check Box 1").Value checking for xlOff\xlOn, not ones or zeroes. Please see The Complete Guide to Excel VBA Form Control Checkboxes

If you change your code to the recommended syntax it works perfectly fine:

Sub Test()

    Dim Fink As Integer
    Fink = 3
    ' Access the checkbox value correctly
    If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOff Then
        Fink = 0
    End If
    If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
        Fink = 1
    End If
    Range("A1").Value = Fink

End Sub

Sub Testy()

    Dim Fink As Integer
    Fink = 3
    ' Access the checkbox value correctly
    If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
        Fink = 1
    End If
    If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOff Then
        Fink = 0
    End If
    Range("A1").Value = Fink

End Sub