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.
Does anyone know if this is possible?
Thanks a lot in advance!
Consider using the TopLeftCell property
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
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
You can then use this code to identify which controls from Sheet1
have left alignment equal to what you need.
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
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 5
and the code checks for any checkbox that is within the bounds of the left most part of column E and column 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
精彩评论