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

 

[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
(e-mail address removed)
http://www.vonixx.com

 

[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

 

[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