Excel VBA 'unable to get checkboxes property' when checkbox clicked on copied sheet

Question

As the title kinda implies, my macro works as intended on the original sheet, but the same macro fails on the copied sheet.

I have an Excel workbook that takes an input from sheet1 to copy and populate sheet2, such that the book now contains sheet1, sheet2, and sheet3 (which is the populated copy of sheet2). Sheet2 contains a bunch of checkboxes, which are copied to sheet3. Sheet2 always has the same name, but the name for sheet3 changes every time.

I wrote a macro in the workbook module to add info to a few different books when each checkbox is clicked. The macro works as expected when the checkbox on sheet2 is clicked, but when I click the checkbox on sheet3, I receive "Run-time error '1004': Unable to get the CheckBoxes property of the Worksheet class".

Here is the line that is getting the error:

sheetname = ActiveSheet.name

If Sheets(sheetname).CheckBoxes("43") = 1 Then

Both checkboxes call the same macro, and the name of the sheet is dictated by 'sheetname = ActiveSheet.Name' and appears correct when used on sheet3.

Things I have tried in vain:

changing "= 1" to "= True"
changing "CheckBoxes" to "OLEObjects"
verified 'sheetname' is correct
Change sheet3 checkbox to ActiveX, applied code
change name of copied checkbox and change code to reflect new name
write macro on sheet2 and/or sheet3 modules
"CheckBoxes("43").Value = 1"

Why does it work on one sheet, and not on the other?

Thank you for your time!

chris neilsen chris neilsen · Accepted Answer · 2018-06-19T23:20:13

When you copy a CheckBox to another sheet, its Name will change. You need another method to identify them, perhaps the Caption?

You can use this little Sub to identify the Names and Captions of Checkboxes on a sheet

Sub LitsNames()
    Dim ws As Worksheet
    Dim cb As CheckBox
    Set ws = ActiveSheet
    For Each cb In ws.CheckBoxes
        Debug.Print "Name = " & cb.Name, "Caption = " & cb.caption
    Next
End Sub

See the results in the Immediate window

This Function will return the Checkbox with a given Caption (if there are more than one CB with a given caption, then this won't work)

Function GetCB(ws As Worksheet, caption As String) As CheckBox
    Dim cb As CheckBox
    For Each cb In ws.CheckBoxes
        If cb.caption = caption Then
            Set GetCB = cb
            Exit Function
        End If
    Next
End Function

Use this like so

Sub Demo()
    Dim cb As CheckBox

    Set cb = Nothing
    Set cb = GetCB(ActiveSheet, "Some Caption")
    If Not cb Is Nothing Then
        If cb.Value = 1 Then
            ' do stuff
        End If
    End If
End Sub

Excel VBA 'unable to get checkboxes property' when checkbox clicked on copied sheet

2 Answers