Selecting Objects

M

mark1

In VBA, how do I select more than one object at the time?
For example the code I have says this:

ActiveSheet.OLEObjects("CheckBox1").Object.Value = 0

What I want to do is something like this:

ActiveSheet.OLEObjects
("CheckBox1,Checkbox2...").Object.Value = 0
 
D

Dave Peterson

If I knew how many checkboxes there were and they were named nicely, I'd do
something like:

Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To 4
Sheet1.OLEObjects("checkbox" & iCtr).Object.Value = False
Next iCtr
End Sub

If I wanted to get them all, but didn't know how many, I could get them this
way:

Sub testme2()

Dim OLEobj As OLEObject

For Each OLEobj In Sheet1.OLEObjects
If TypeOf OLEobj.Object Is MSForms.CheckBox Then
OLEobj.Object.Value = False
End If
Next OLEobj

End Sub

If you knew the names of just the checkboxes you wanted to change:

Sub testme3()
Dim iCtr As Long
Dim myCBXNames As Variant
myCBXNames = Array("checkbox1", "checkbox2")
For iCtr = LBound(myCBXNames) To UBound(myCBXNames)
Sheet1.OLEObjects(myCBXNames(iCtr)).Object.Value = False
Next iCtr
End Sub
 
Top