Manipulating a checkbox by name dynamically

[Eliezer]

I have a bunch of checkboxes (not an array) that have
names like: rep1_cb, rep2_cb, rep3_cb, etc. I need to
iterate through all of them and change their captions.

My code looks something like this:

Dim counter as integer
counter = 1
Do Until...
Sheet1.OLEObjects("rep" & counter & "_cb").Object.???
counter = counter + 1
Loop


I don't know the syntax here for this. Is "Sheet1" the
correct thing to use altogether?

Thanks!
Eliezer

 

[Dave Peterson]

In your example, Sheet1 would be the codename of the worksheet.

When you're in the VBE, hit ctrl-r to see the project explorer for your project.
Expand it to see the Microsoft Excel Objects.

You'll see items like:
Sheet1 (MySheetName)

The name to the left is the code name. The name in parentheses is the name you
can see on the worksheet tab.

And you could have used:
worksheets("mySheetName").oleobjects.....
if you knew that the worksheet name wouldn't change.

If I knew how many checkboxes there were, I'd do something like:

Option Explicit
Sub testme()

Dim iCtr As Long

For iCtr = 1 To 4
Sheet1.OLEObjects("rep" & iCtr & "cb").Object.Caption = "Hi_" & iCtr
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.Caption = "Hi_there"
End If
Next OLEobj

End Sub