Hi everyone,

I am using a Form Spinner Control, and I want the maximum number to be the
(variable) number in a particular cell, but I can't seem to find any way of
setting a maximum number other than actually manually typing a specific
number into the 'Format Control' dialogue box.

I'm hoping and praying that I'm not going to be told I have to write a
macro, as this is a one-off and I don't particularly want to spend hours and
hours learning how to write macros for a one-off :-(

Thank you to anyone who can help

Paste the following to the worksheet's class module. Right click the
worksheet tab and select "View Code" to access the code module. Change the
cell reference ("$A$1") to suit and name of the spinner ("Spinner 1").

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
Me.Shapes("Spinner 1").ControlFormat.Max = .Value
End If
End With
End Sub

Hi Greg,

Thank you so much for that. I'm afraid I miss-informed you, and I actually
meant a Scroll-bar, and not like I said, a 'Spinner'. (I know, it doesn't
help when people give you the wrong information!). However, I used yours and
just substituted 'Scroll Bar' for 'Spinner', but I couldn't get it to work,
until after some research on the net, I changed 'Me.Shapes' to
'ActiveSheet.Shapes' (more by lucky guess than an understanding!), and it
worked. I just want to understand why? The code that works is he

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$C$2" Then
ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Max = .Value
End If
End With
End Sub

Should the same function have worked on a Scroll Bar the same as it does on
a Spinner, or is there some inherent difference between them that means I
should have made the change from 'Me.Shapes' to 'ActiveSheet.Shapes' ?

Thank you again so much for your help.

Darrell.


----------------------------------------------------------------------------------------------------------------------


"Greg Wilson" wrote in message
...
Paste the following to the worksheet's class module. Right click the
worksheet tab and select "View Code" to access the code module. Change the
cell reference ("$A$1") to suit and name of the spinner ("Spinner 1").

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
Me.Shapes("Spinner 1").ControlFormat.Max = .Value
End If
End With
End Sub

Are you sure that you were changing the right cell (C2) when you did your
tests using "Me"? I have both working. Insert "MsgBox Address" after "With
Target" if in doubt.

Me is just shorter than ActiveSheet. To answer your question: No, it
shouldn't make a difference as long as they are both from the Forms toolbar.
And if they were from the Control Toolbox toolbar it wouldn't work at all.

Greg

"Darrell Burnett" wrote:

Hi Greg,

Thank you so much for that. I'm afraid I miss-informed you, and I actually
meant a Scroll-bar, and not like I said, a 'Spinner'. (I know, it doesn't
help when people give you the wrong information!). However, I used yours and
just substituted 'Scroll Bar' for 'Spinner', but I couldn't get it to work,
until after some research on the net, I changed 'Me.Shapes' to
'ActiveSheet.Shapes' (more by lucky guess than an understanding!), and it
worked. I just want to understand why? The code that works is he

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$C$2" Then
ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Max = .Value
End If
End With
End Sub

Should the same function have worked on a Scroll Bar the same as it does on
a Spinner, or is there some inherent difference between them that means I
should have made the change from 'Me.Shapes' to 'ActiveSheet.Shapes' ?

Thank you again so much for your help.

Darrell.


----------------------------------------------------------------------------------------------------------------------


"Greg Wilson" wrote in message
...
Paste the following to the worksheet's class module. Right click the
worksheet tab and select "View Code" to access the code module. Change the
cell reference ("$A$1") to suit and name of the spinner ("Spinner 1").

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$1" Then
Me.Shapes("Spinner 1").ControlFormat.Max = .Value
End If
End With
End Sub