Checking if a worksheet-based checkbox is checked

I'm trying to use an IF-clause to determine whether my checkbox, named "Check Box 1", is checked.

My current code:

Sub Button167_Click()
 If ActiveSheet.Shapes("Check Box 1") = True Then
 Range("Y12").Value = 1
 Else
 Range("Y12").Value = 0
 End If
End Sub

This doesn't work. The debugger is telling me there is a problem with the

ActiveSheet.Shapes("Check Box 1")

However, I know this code works (even though it serves a different purpose):

ActiveSheet.Shapes("Check Box 1").Select
With Selection
.Value = xlOn

My checkboxes (there are 200 on my page), are located in sheet1, by the name of "Demande". Each Checkbox is has the same formatted name of "Check Box ...".

Solution 1:

Sub Button167_Click()
 If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
 Range("Y12").Value = 1
 Else
 Range("Y12").Value = 0
 End If
End Sub

1 is checked, -4146 is unchecked, 2 is mixed (grey box)

Solution 2:

Is this what you are trying?

Sub Sample()
    Dim cb As Shape

    Set cb = ActiveSheet.Shapes("Check Box 1")

    If cb.OLEFormat.Object.Value = 1 Then
        MsgBox "Checkbox is Checked"
    Else
        MsgBox "Checkbox is not Checked"
    End If
End Sub

Replace Activesheet with the relevant sheetname. Also replace Check Box 1 with the relevant checkbox name.

Solution 3:

Building on the previous answers, you can leverage the fact that True is -1 and False is 0 and shorten your code like this:

Sub Button167_Click()
  Range("Y12").Value = _
    Abs(Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0)
End Sub

If the checkbox is checked, .Value = 1.

Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0 returns True.

Applying the Abs function converts True to 1.

If the checkbox is unchecked, .Value = -4146.

Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value > 0 returns False.

Applying the Abs function converts False to 0.

Solution 4:

It seems that in VBA macro code for an ActiveX checkbox control you use

If (ActiveSheet.OLEObjects("CheckBox1").Object.Value = True)

and for a Form checkbox control you use

If (ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = 1)