Checking if a worksheet-based checkbox is checked

Question

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 ...".

Answer 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)

Answer 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.