How to find and select Checkbox in Excel using VBA

Question

I have a workbook with many worksheets and hundreds of checkboxes, toggles, etc.

Somewhere in the code I have inherited

"FlashCopy_chkbox.Enabled = False"

doesn't work. I need to find WHERE that checkbox/toggle is in a previous version of the same file.

Simply put, how to I use vba to select whatever that is referencing and center it on my screen?

Answer 1

The following code will list all ActiveX controls on all sheets in a workbook in the Immediate window of the VBE. Furthermore, the code will list all form control OptionButtons, CheckBoxes, and Buttons on all sheets:

Option Explicit

Public Sub FindThemAll()

Dim ws As Worksheet
Dim obj As OLEObject
Dim opt As OptionButton
Dim chk As CheckBox
Dim cmd As Button

For Each ws In ThisWorkbook.Worksheets
    'Handling all ActiveX controls
    For Each obj In ws.OLEObjects
        Debug.Print "---------------------------------------------"
        Debug.Print "ActiveX component on sheet: " & ws.Name
        Debug.Print "Location on sheet: " & obj.TopLeftCell.Address
        Debug.Print "Name of the component: " & obj.Name
        Debug.Print "Object type: " & TypeName(obj.Object)
    Next obj
    'Handling Form Controls
    For Each opt In ws.OptionButtons
        Debug.Print "---------------------------------------------"
        Debug.Print "Form control on sheet: " & ws.Name
        Debug.Print "Location on sheet: " & opt.TopLeftCell.Address
        Debug.Print "Name of the component: " & opt.Name
        Debug.Print "Object type: OptionButton"
    Next opt
    For Each chk In ws.CheckBoxes
        Debug.Print "---------------------------------------------"
        Debug.Print "Form control on sheet: " & ws.Name
        Debug.Print "Location on sheet: " & chk.TopLeftCell.Address
        Debug.Print "Name of the component: " & chk.Name
        Debug.Print "Object type: CheckBox"
    Next chk
    For Each cmd In ws.Buttons
        Debug.Print "---------------------------------------------"
        Debug.Print "Form control on sheet: " & ws.Name
        Debug.Print "Location on sheet: " & cmd.TopLeftCell.Address
        Debug.Print "Name of the component: " & cmd.Name
        Debug.Print "Object type: Button"
    Next cmd
Next ws

End Sub

Let me know if you have any questions.

Answer 2

You can not just call a control on a worksheet directly from outside of the worksheets module. Is FlashCopy_chkbox declared somewhere in your script? Set FlashCopy_chkbox = Worksheet("Sheet1").FlashCopy_chkbox.

In any case this will find it.

Sub LookForFlash()
    Dim ws As Worksheet
    Dim ck As OLEObject

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set ck = ws.OLEObjects("FlashCopy_chkbox")
        If Err = 0 Then
            MsgBox ws.Name
        End If
        On Error GoTo 0
    Next ws
End Sub

I understand you were looking for a built in method but In the future please post some code. Stack Overflow is here to help improve existing code.