Form Spinner, up or down pressed?

catch · #1 07-04-2009, 08:17 AM

Using Excel 2007 Form Spinners (i.e. not ActiveX ones) how can I detect whether the user has pressed the up or the down button? I can get the associated value of it, but I would like to use the control independent from the actual spinner. In other words, I have various spinners on my sheet and would like cells to move up or down (not incremental, through specific vba code instead) depending on which spinners is pressed and in which direction.
I can happily identify the spinners through ActiveSheet.Spinners(Application.Caller).TopLeftCell.Row (and .Column), but the value property is capped through Min and Max settings, so this doesnt necessarily tell me which direction the user pressed it.
Thanks for any clues in advance

shg · #2 07-04-2009, 01:08 PM

Wouldn't it be easier to use ActiveX spin buttons?

Andy Pope · #3 07-04-2009, 02:41 PM

Min value=0
Max value=1

Code:

Sub Spinner2_Change()

    With ActiveSheet.Shapes(Application.Caller)
        If .ControlFormat.Value = 1 Then
            MsgBox "Up"
        Else
            MsgBox "Down"
        End If
    End With
        
End Sub

catch · #4 07-08-2009, 11:49 AM

Many Thanks for your replies.

The last one worked well (just setting Min=0 and Max=1, therefore knowing what was pressed).

Two related follow ups

1. Would it have been easier using ActiveX version (i.e. first reply) and if so how?

2. For a spin button on a pop-up form, how can I do the same thing? There I only tend to get SpinButton1_Change which cant be tricked with the 0/1. (If its 0 already, then pressing down doesnt trigger the event). I was thinking of having min=0, max=2 and always setting it to 1 at the end of it, but this itself triggers another call to SpinButton1_Change. Is this the way forward (if yes, how do I disable this recursive calling) or is there a better way?

Andy Pope · #5 07-08-2009, 12:06 PM

On a userform use SpinUp and SpinDown events.

ActiveX also has these 2 events, which would have made it easier.

catch · #6 07-09-2009, 04:46 AM

Thanks, they work fine there. Reason why I didnt use them for the sheet was that I had hundreds of spin buttons. From what I gather the ActiveX ones automatically link to Spin253_SpinUp etc., so I would have a huge number of those functions. Using the other ones I can link them all to one user defined function where I identify the object through Application Caller.

Welcome to the Excel Forum

Forum Statistics