Run Different Macros for the Up & Down Spinner Form Buttons
 

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

Run Different Macros for the Up & Down Spinner Form Buttons
 

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" wrote in message
...
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

Run Different Macros for the Up & Down Spinner Form Buttons
 

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.

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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Run Different Macros for the Up & Down Spinner Form Buttons
 

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

"bony_tony" wrote in message
...
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.

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" wrote in message

...



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- Hide quoted text -

- Show quoted text -