是否可以在 Excel VBA 中返回复选框的名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6019180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to return the names of checkboxes in Excel VBA?
提问by Marc
I'm currently working with a couple of worksheets that contain hundreds of checkboxes. The code behind these checkboxes works fine, but I'm looking for a way to list the names of the checkboxes per column, i.e. I need to know the names of all checkboxes in column G, for instance.
我目前正在处理几个包含数百个复选框的工作表。这些复选框背后的代码工作正常，但我正在寻找一种方法来列出每列复选框的名称，例如，我需要知道 G 列中所有复选框的名称。
Does anyone know if this is possible?
有谁知道这是否可能？
Thanks a lot in advance!
非常感谢！
回答by Dick Kusleika
Consider using the TopLeftCell property
考虑使用 TopLeftCell 属性
Sub ListCheckBoxes()
Dim ole As OLEObject
'Loop through all the active x controls
For Each ole In Sheet1.OLEObjects
'Only care about checkboxes
If TypeName(ole.Object) = "CheckBox" Then
'Check topleftcell property
If ole.TopLeftCell.Column = Sheet1.Range("G1").Column Then
'print out list
Debug.Print ole.TopLeftCell.Address, ole.Name
End If
End If
Next ole
End Sub
回答by Alex P
The code below will work if you specify the column you want to check.
如果您指定要检查的列，下面的代码将起作用。
For example, if you want to search for all checkboxes in column E you specify 5and the code checks for any checkbox that is within the bounds of the left most part of column E and column F.
例如，如果您要搜索 E 列中的所有复选框，您指定5并且代码检查 E 列和 F 列最左侧范围内的任何复选框。
Sub ListCheckBoxNames()
Dim col As Long, cb As OLEObject
col = 5 //e.g. A=1, B=2, C=3 etc...you need to change this as appropriate
For Each cb In Worksheets(1).OLEObjects
If cb.Left >= Columns(col).Left And cb.Left < Columns(col + 1).Left Then
Debug.Print cb.Name
End If
Next cb
End Sub
回答by osknows
If you align a control to column G (hold down ALT whilst moving to align) select properties and find out the left position of the control
如果您将控件与 G 列对齐（在移动以对齐的同时按住 ALT）选择属性并找出控件的左侧位置
You can then use this code to identify which controls from Sheet1have left alignment equal to what you need.
然后，您可以使用此代码来确定哪些控件的Sheet1左对齐与您需要的相同。
Sub test()
lngcolumnGLeft = 288 'pseudo position of object aligned to column G
'cycle through all objects
With Sheet1
For Each obj In .OLEObjects
If obj.Left = lngcolumnGLeft Then
.Range("G" & .Rows.Count).End(xlUp).Offset(1, 0).Value = obj.Name
End If
Next obj
End With
End Sub
