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