如何使用VBA在Excel中查找和选择复选框 [英] How to find and select Checkbox in Excel using VBA

问题描述

我有一本工作簿，其中包含许多工作表以及数百个复选框，切换键等.

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

在我继承的代码中的某个地方

Somewhere in the code I have inherited

"FlashCopy_chkbox.Enabled = False"

"FlashCopy_chkbox.Enabled = False"

不起作用.我需要在同一文件的先前版本中找到复选框/切换的位置.

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

简单地说，如何使用vba选择要引用的内容并将其居中显示在屏幕上?

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

推荐答案

以下代码将在VBE的 Immediate 窗口中列出工作簿中所有工作表上的所有ActiveX控件.此外，该代码还将在所有工作表上列出所有表单控件 OptionButtons ， CheckBoxes 和 Buttons :

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.

这篇关于如何使用VBA在Excel中查找和选择复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！