Manipulating a checkbox by name dynamically
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
Eliezer wrote:
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
|
