Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get 2003 Macros and Toolbar Buttons form Excel 2003 to 20 | New Users to Excel | |||
Maximum number in Form Spinner Control ? | Excel Worksheet Functions | |||
Exporting excel charts with form controls (spinner) to Powerpoint | Charts and Charting in Excel | |||
Form buttons and macros | Excel Programming | |||
Format of a textbox on a user form controled by a spinner | Excel Programming |