Run Different Macros for the Up & Down Spinner Form Buttons

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bony_tony - 17 Apr 2008 11:38 GMT

How do I assign different macros to up & down spinner buttons?
I want to imporve on the standard up/down by integers the standard
control offers by writing some code myself, but I can only assign a
macro to the whole control (Up & Down)

Cheers
Tony

Reply to this Message

Peter T - 17 Apr 2008 12:21 GMT

Compare the current value with the last value that you had stored, eg in
some cell or in a Name, then update the last value.

Assign "SpinnerTest" as macro to a Spinner from the Forms toolbar.

Sub SpinnerTest()
Dim val As Long
Dim sp As Spinner
Dim rLastVal As Range

Set rLastVal = Range("A1")

Set sp = ActiveSheet.Spinners(Application.Caller)
' or if only applicable to one spinner
' Set sp = ActiveSheet.Spinners("Spinner 1")

With sp
If Len(rLastVal.Value) = 0 Then
rLastVal = .Value
MsgBox "Initialising " & .Value
ElseIf .Value = rLastVal Then
MsgBox "Still at " & IIf(.Value = .Min, "Min:" & .Min, "Max:" &
.Max)
ElseIf .Value > rLastVal Then
MsgBox "Going up " & .Value, , "Last " & rLastVal
Else
MsgBox "Going down " & .Value, , "Last " & rLastVal
End If
rLastVal = .Value
End With

End Sub

Regards,
Peter T

> How do I assign different macros to up & down spinner buttons?
> I want to imporve on the standard up/down by integers the standard
[quoted text clipped - 3 lines]
> Cheers
> Tony

Reply to this Message

bony_tony - 17 Apr 2008 12:41 GMT

Thanks.
I didn't really want to store any values in the sheet.
I've just noticed I have some "ActiveX" controls where I could use
"ScrollBar1_KeyUp". I have Excel 2007. Will this work with Excel 97+?

Cheers
Tony

> Compare the current value with the last value that you had stored, eg in
> some cell or in a Name, then update the last value.
[quoted text clipped - 41 lines]
>
> - Show quoted text -

Reply to this Message

Peter T - 17 Apr 2008 13:05 GMT

As I mentioned you could store the last value in a Name (named formula)
rather than in a cell. Alternatively you could store in a static variable in
your project, it would only need to be initialized first time in a session.

Yes Excel 97+ have ScrollBar1_KeyUp and SpinButton1_KeyUp (as you originally
asked about a spinner). Although these events will indicate an arrow press,
say vbkeyup or vbkeydown, they won't indicate if mouse clicked up/down.

Regards,
Peter T

Thanks.
I didn't really want to store any values in the sheet.
I've just noticed I have some "ActiveX" controls where I could use
"ScrollBar1_KeyUp". I have Excel 2007. Will this work with Excel 97+?

Cheers
Tony

On Apr 17, 12:21 pm, "Peter T" <peter_t@discussions> wrote:

> Compare the current value with the last value that you had stored, eg in
> some cell or in a Name, then update the last value.
[quoted text clipped - 45 lines]
>
> - Show quoted text -

Reply to this Message