Referencing objects inside of a loop

[cubbybear3]

My next problem is:
I have a series of spin buttons (spnVar1, spnVar2, spnVar3) and would
like to reference/change their properties inside a loop. I know this
will not work, but it is basiclly what I need. Also, the spin buttons
do NOT have linked cells.

for each X = 1 to 3
' initialize the value
spnVar(X).Max = X
' reference the value
sheets("Wksht").range("A" & X) = spnVar(X).Min
next X

 

[Dave Peterson]

Are these objects on a worksheet?
If yes, are they spinners from the Forms toolbar or from the Control toolbox
toolbar?

 

[cubbybear3]

Are these objects on a worksheet?
If yes, are they spinners from the Forms toolbar or from the Control toolbox
toolbar?

Dave, I am sorry I am so late in answering your question (lost my DSL
connection). They are on the worksheet and are from the Controls
toolbar/toolbox. I have done this for option buttons on a Form, but
can't seem to get it to work for objects on a worksheet. Should I
have created them from the Forms toolbar?

 

[Dave Peterson]

You could use something like:

Dim iCtr As Long
For iCtr = 1 To 3
Worksheets("Sheet1").OLEObjects("Spinbutton" & iCtr).Object.Min = 100
Next iCtr

Or if you want to modify all the spinbuttons on that sheet:

Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("Sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.SpinButton Then
OLEObj.Object.Max = 100
End If
Next OLEObj