Changing the SpinButton value in Workbook_Open()

[Tony Steane]

Greetings One and All,

I have two SpinButtons on a worksheet and I am trying to change the
value of each button as I open the Workbook. Each button will have
a different value assigned.

I have tried the obvious :

Private Sub Workbook_Open()

Spinbotton1.value = 10
Spinbutton2.value = 24 both are random numbers each time.

end sub

However I recieve the error "didn't provide a valid object qualifier".

My knowledge is such that I am unable to find out how this is done.

Would somebody please provide me with a solution and if possible a web
site where this topic is discussed. ( in simple terms if possible.)

Cheers

Tony

[theDude]

Tony,

You need to provide a reference (change name in BOLD below to your own reference) to the spinbutton object:

Private Sub Workbook_Open()

ActiveWorkbook.Worksheets("your_sheet_name").Spinbutton1.value = 10
ActiveWorkbook.Worksheets("your_sheet_name").Spinbutton2.value = 24
end sub

Hope this helps,
theDude

[Edwin Tam]

Changing the properties of controls on a worksheet is slightly different
from changing controls on an UserForm.

The main difference is the way controls should be called.

I assume you have two "Spinners" in Sheet1.
See the macro below.

Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Sheet1")
.DrawingObjects("Spinner 1").Value = 10
.DrawingObjects("Spinner 2").Value = 100
End With
End Sub

"DrawingObjects" is a very safe way to refer to controls on a worksheet. All
controls can be referred to as DrawingObjects.


Regards,
Edwin Tam
edwintam@vonixx.com
http://www.vonixx.com



On 1/15/05 1:08 PM, in article
c0e1827f.0501142108.353ea4e5@posting.google.com, "Tony Steane"
<tsteane@hotmail.com> wrote:

> Greetings One and All,
>
> I have two SpinButtons on a worksheet and I am trying to change the
> value of each button as I open the Workbook. Each button will have
> a different value assigned.
>
> I have tried the obvious :
>
> Private Sub Workbook_Open()
>
> Spinbotton1.value = 10
> Spinbutton2.value = 24 both are random numbers each time.
>
> end sub
>
> However I recieve the error "didn't provide a valid object qualifier".
>
> My knowledge is such that I am unable to find out how this is done.
>
> Would somebody please provide me with a solution and if possible a web
> site where this topic is discussed. ( in simple terms if possible.)
>
> Cheers
>
> Tony

[Tom Ogilvy]

If the were spinbuttons from the control toolbox toolbar, I think you would
have to use:

With ThisWorkbook.Worksheets("Sheet1")
.DrawingObjects("Spinner 1").Object.Value = 10
.DrawingObjects("Spinner 2").Object.Value = 100
End With

--
Regards,
Tom Ogilvy

"Edwin Tam" <edwintam@vonixx.com> wrote in message
news:BE0EF718.1908%edwintam@vonixx.com...
> Changing the properties of controls on a worksheet is slightly different
> from changing controls on an UserForm.
>
> The main difference is the way controls should be called.
>
> I assume you have two "Spinners" in Sheet1.
> See the macro below.
>
> Private Sub Workbook_Open()
> With ThisWorkbook.Worksheets("Sheet1")
> .DrawingObjects("Spinner 1").Value = 10
> .DrawingObjects("Spinner 2").Value = 100
> End With
> End Sub
>
> "DrawingObjects" is a very safe way to refer to controls on a worksheet.
All
> controls can be referred to as DrawingObjects.
>
>
> Regards,
> Edwin Tam
> edwintam@vonixx.com
> http://www.vonixx.com
>
>
>
> On 1/15/05 1:08 PM, in article
> c0e1827f.0501142108.353ea4e5@posting.google.com, "Tony Steane"
> <tsteane@hotmail.com> wrote:
>
> > Greetings One and All,
> >
> > I have two SpinButtons on a worksheet and I am trying to change the
> > value of each button as I open the Workbook. Each button will have
> > a different value assigned.
> >
> > I have tried the obvious :
> >
> > Private Sub Workbook_Open()
> >
> > Spinbotton1.value = 10
> > Spinbutton2.value = 24 both are random numbers each time.
> >
> > end sub
> >
> > However I recieve the error "didn't provide a valid object qualifier".
> >
> > My knowledge is such that I am unable to find out how this is done.
> >
> > Would somebody please provide me with a solution and if possible a web
> > site where this topic is discussed. ( in simple terms if possible.)
> >
> > Cheers
> >
> > Tony
>

[Bob Phillips]

Tony,

It depends upon the spinner that you use.

If you use the one from the control toolbox, you can use

activesheet.oleobjects("SpinButton1").object.value =10

If you use the one from the forms toolbar, you can use

activesheet.spinners("Spinner 1").value=9

--
HTH

Bob Phillips

"Tony Steane" <tsteane@hotmail.com> wrote in message
news:c0e1827f.0501142108.353ea4e5@posting.google.com...
> Greetings One and All,
>
> I have two SpinButtons on a worksheet and I am trying to change the
> value of each button as I open the Workbook. Each button will have
> a different value assigned.
>
> I have tried the obvious :
>
> Private Sub Workbook_Open()
>
> Spinbotton1.value = 10
> Spinbutton2.value = 24 both are random numbers each time.
>
> end sub
>
> However I recieve the error "didn't provide a valid object qualifier".
>
> My knowledge is such that I am unable to find out how this is done.
>
> Would somebody please provide me with a solution and if possible a web
> site where this topic is discussed. ( in simple terms if possible.)
>
> Cheers
>
> Tony

[d_h_sanjay]

maybe
please check the spelling of spinbutton

its been mistakenly typed as spinbotton in the first line

its o instead of u

[Tony Steane]

Many Thanks to each of you for your assistance.

In this case, it was as Bob suggested that:

If you use the one from the control toolbox, you can use
activesheet.oleobjects("SpinButton1").object.value =10

It seems that when asking any question you really have to be precise. As
in this case a "spin button" has two meanings.

Ever again I will continue to try to work through this maze they call
Excel :-)

Cheers and Thanks

Tony

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!