Maximum number in Form Spinner Control ?

[Darrell Burnett]

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

 

[Greg Wilson]

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

 

[Darrell Burnett]

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 here:

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.


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


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

 

[Greg Wilson]

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