Loop through SpinButtons on a Worksheet

[Rob]

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

 

[Jim Cone]

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

 

[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

 

[Rob]

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

Thanks Again!

 

[Jim Cone]

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"
<[email protected]>
wrote in message
BTW.. in my example immediately above.. all shapes in the sheet are
SpinButtons.
Thanks Again!

 

[Rob]

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

-Rob