Excel VBA Checkbox Doesn't Support This Property or Method

Question

I'm writing a macro that manipulates some checkboxes on my Excel doc. When Checkbox1 is clicked, I want to uncheck Checkbox2 and Checkbox3, as well as changing the value of a cell elsewhere in the workbook. On the Checkbox2 line, I'm getting an error Object doesn't support this property or method.

Public Sub CheckBox1_Click()
    Sheets("Sheet2").CheckBox2.Enabled = False
    Sheets("Sheet2").CheckBox3.Enabled = False
    Sheets("Sheet3").Range("A22").Value = Sheets("Sheet3").Range("B7").Value
End Sub

Then you're working with a form control checkbox, which is different from an ActiveX checkbox. — BigBen, Feb 19 '21 at 01:12
possibly helpful: [**Differences between Excel's Form Controls & ActiveX Controls**](https://stackoverflow.com/a/50144021/8112776) — ashleedawg, Aug 27 '22 at 12:08

score 1 · Answer 1 · answered Feb 19 '21 at 02:44

Form controls are still available in Excel for backward compatibility. It's recommended to use ActiveX controls in their stead. Moreover, if you wish to allow only one choice the appropriate choice of weapon is the Option Button which has the functionality you want built-in. However, if you have your reasons for continuing on the path you have set out on then the code below will do what you want.

Sub CheckBox_Click()
    ' 177
    
    Dim BoxId           As Variant                  ' ID of the clicked check box
    Dim Chk             As Shape                    ' clicked checkbox
    Dim ChkVal          As MsoTriState              ' value of Chk
    Dim i               As Integer                  ' loop counter: BoxIds
    Dim Tmp             As String
    
    With Worksheets("Sheet1")                       ' change to suit
        Set Chk = .Shapes(Application.Caller)
        ChkVal = Chk.OLEFormat.Object.Value
        BoxId = Array(2, 6, 7)                      ' change and add as required
    
        For i = 0 To UBound(BoxId)
            Tmp = "Check Box " & Trim(BoxId(i))
            If Tmp <> Chk.Name Then
                .Shapes(Tmp).OLEFormat.Object.Value = 0
            End If
        Next i
    End With
    
    With Worksheets("Sheet3")
        ' removes value from A22 if checkbox is unchecked
        .Range("A22").Value = IIf(ChkVal, .Range("B7").Value, "")
    End With
End Sub

I have added the variable ChkVal to the mix which is the value of the clicked checkbox. I then used that value to either write the value to Sheet3!A22 or clear that cell.

Note that in my worksheet I created ActiveX as well as Form Control check boxes and Excel used the same numbering sequence for both. Therefore I ended up with form control check boxes Nos 2, 6 and 7. Please check the numbers in the code to suit your own worksheet. You can also add more check boxes to the array.

Excel VBA Checkbox Doesn't Support This Property or Method

1 Answers1