How do you define SpinButtons??

[Guest]

I have used the control toolbox to create a series of radio buttons. For one
of the radio buttons, when i click on it I want it to creat an OLEobject. I
created a privet sub program like the one below:

Private Sub Warrior_Click()

ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=601.5, Top:=282, Width:=27, Height _
:=11.25).Select

End Sub

When I use the radio button it goes ahead and makes this spinbutton,
however, the spinbutton gets set to the default name "SpinButton1" and also
the defalt Max which is 100. I would like to create the spinbutton and set
the name and max number to what I want. How do you do this? Is it possible?

 

[Guest]

Private Sub Warrior_Click()

ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=601.5, Top:=282, Width:=27, Height _
:=11.25).Select
End Sub

When I use the radio button it goes ahead and makes this spinbutton,
however, the spinbutton gets set to the default name "SpinButton1" and also
the defalt Max which is 100. I would like to create the spinbutton and set
the name and max number to what I want. How do you do this? Is it possible?

It's a little tricky dealing with spreadsheet controls through the OLEObject
container. Does this get close to what you're looking for?

Private Sub Warrior_Click()
Dim NewOLESpinner As OLEObject
Dim TheSpinner As MSForms.SpinButton

Set NewOLESpinner = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.SpinButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=601.5, Top:=282, _
Width:=27, Height:=11.25)

' pull the spinner object out of the OLE container
Set TheSpinner = NewOLESpinner.Object
' rename the spinner, set its .Max
TheSpinner.Name = "sheetSpinnerName"
TheSpinner.Max = 50
TheSpinner.LinkedCell = "A5" ' etc.
End Sub