Prev: reset the value of a spin button
Next: UPC Numbers
From: cubbybear3 on 7 Apr 2010 18:29
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
From: Dave Peterson on 7 Apr 2010 20:28
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
From: cubbybear3 on 9 Apr 2010 01:06
On Apr 7, 5:28 pm, Dave Peterson <peter...(a)verizonXSPAM.net> 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?
From: Dave Peterson on 9 Apr 2010 07:59
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 <peter...(a)verizonXSPAM.net> 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
 | 
Pages: 1
Prev: reset the value of a spin button
Next: UPC Numbers