Referencing objects inside of a loop
 

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

Referencing objects inside of a loop
 

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



cubbybear3 wrote:

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

Referencing objects inside of a loop
 

On Apr 7, 5:28*pm, Dave Peterson wrote:
Are these objects on a worksheet?
If yes, are they spinners from the Forms toolbar or from the Control toolbox
toolbar?

cubbybear3 wrote:

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

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?

Referencing objects inside of a loop
 

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





cubbybear3 wrote:

On Apr 7, 5:28 pm, Dave Peterson wrote:
Are these objects on a worksheet?
If yes, are they spinners from the Forms toolbar or from the Control toolbox
toolbar?

cubbybear3 wrote:

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

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