Run Different Macros for the Up & Down Spinner Form Buttons

[bony_tony]

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

 

[Peter T]

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

 

[bony_tony]

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

 

[Peter T]

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