Check Boxes

O

OMG

I've placed several check boxes in a worksheet (not on a form). What I want to do through VBA is to iterate through all the check boxes and, where the "Value" property returns True then read the caption. My problem is that I can't figure out how to iterate through the boxes efficiently. With the worksheet name of "Parameters" I've tried "Parameters.CheckBox1.Value" but I want to be able to just get a collection of all checkboxes on the page and iterate

Any ideas
 
C

Chip Pearson

If these are checkboxes from the Forms toolbar, use code like the
following:

Dim CHK As Excel.CheckBox
For Each CHK In ActiveSheet.CheckBoxes
If CHK.Value = 1 Then
MsgBox CHK.Caption
End If
Next CHK

If these are checkboxes from the Controls toolbar, use code like
the following:

Dim Obj As OLEObject
For Each Obj In ActiveSheet.OLEObjects
If TypeOf Obj.Object Is MSForms.CheckBox Then
If Obj.Object.Value Then
MsgBox Obj.Object.Caption
End If
End If
Next Obj


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




OMG said:
I've placed several check boxes in a worksheet (not on a form).
Click to expand...
What I want to do through VBA is to iterate through all the check
boxes and, where the "Value" property returns True then read the
caption. My problem is that I can't figure out how to iterate
through the boxes efficiently. With the worksheet name of
"Parameters" I've tried "Parameters.CheckBox1.Value" but I want
to be able to just get a collection of all checkboxes on the page
and iterate.
 
Top