Hi,

Is it possible to create a For Each statement to find all of the
SpinButtons on a specific sheet? I know the following code would find
the SpinButtons as shapes, but I need to have them as SpinButtons so
that I can programmatically alter the Max and SmallChange members of
the SpinButton class.

ie this will find the shapes, but not the SpinButtons:

Sub FindShapes
dim shp as shape
dim shps as shapes
set shps = Sheets(1).Shapes
For Each shp in shps
msgbox(shp.name)
Next
set shps = Nothing
End SUb

Any help would be greatly appreciated.. sorry if this is extremely
elementary but I am a Finance guy, not a programmer!

Thanks,
Rob

A shape object has a "ControlFormat" property.
You can use that to access the properties of the actual control.
x = shp.ControlFormat.SmallChange
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Rob"
wrote in message
Hi,
Is it possible to create a For Each statement to find all of the
SpinButtons on a specific sheet? I know the following code would find
the SpinButtons as shapes, but I need to have them as SpinButtons so
that I can programmatically alter the Max and SmallChange members of
the SpinButton class.

ie this will find the shapes, but not the SpinButtons:

Sub FindShapes
dim shp as shape
dim shps as shapes
set shps = Sheets(1).Shapes
For Each shp in shps
msgbox(shp.name)
Next
set shps = Nothing
End SUb

Any help would be greatly appreciated.. sorry if this is extremely
elementary but I am a Finance guy, not a programmer!
Thanks,
Rob

Thanks for your reply. If I use the following code:

Sub SetSpinButtonParameters()
Dim Shp As Shape
Dim Shps As Shapes
Dim Sht As Worksheet
Set Sht = Sheets(1)
Set Shps = Sht.Shapes
For Each Shp In Shps
With Shp.ControlFormat
.Max = 100
.SmallChange = 10
End With
Next
Set Shps = Nothing
Set Sht = Nothing
Set Shp = Nothing
Set Sht_c = Nothing
End Sub

I get <Object doesn't support this property or method when the code
tries to set the max. I am not sure how to interpret this.

Thanks!
Rob

BTW.. in my example immediately above.. all shapes in the sheet are
SpinButtons.

Thanks Again!

There are two types of controls that can be added to a worksheet.
This should work for the type you have...

Sub TellMeTheTruth()
Dim O_Object As OLEObject
For Each O_Object In ActiveSheet.OLEObjects
If TypeOf O_Object.Object Is MSForms.SpinButton Then
MsgBox O_Object.Object.SmallChange
End If
Next
End Sub


"Rob"

wrote in message
BTW.. in my example immediately above.. all shapes in the sheet are
SpinButtons.
Thanks Again!

That's just what I needed. Thanks for your help Jim!

-Rob