Solved: Spinner Button

[Opv]

Is there a way to control the UP and DOWN events for a spinner button created from the Form toolbar as opposed to inserting a spinner button form within VBA? A form created in VBA has the Private Sub SpinButton1_SpinUp() and Private Sub SpinButton1_SpinDown(); however, I haven't found a way to distinguish the up or down direction when I use a spinner button created from the toolbar (Excel 2000). When I test my spinner button with Application.Caller it shows the same result regardless of the up or down direction clicked.

[Opv]

So far, the following code is doing what I want it to do. I'll leave the thread open for a while in the event someone has a better or more succinct solution.


Dim lastVal As Integer


Sub createStatement()

Dim sp As Spinner, forMonth As Range, rptMonth As Date

Set sp = ActiveSheet.Spinners(Application.Caller)
lastVal = lastVal
rptMonth = Range("ReportMonth")
Set forMonth = Range("ReportMonth")

With sp

If Len(lastVal) = 0 Then

lastVal = lastVal - 1

ElseIf .Value > lastVal Then

forMonth.Value = DateSerial(Year(rptMonth), Month(rptMonth) + 2, 0)

Else

forMonth.Value = DateSerial(Year(rptMonth), Month(rptMonth), 0)

End If

lastVal = .Value

End With

closeIt:

End Sub

[Opv]

So far, the following code is doing what I want it to do. I'll leave the thread open for a while in the event someone has a better or more succinct solution.


Dim lastVal As Integer


Sub createStatement()

Dim sp As Spinner, forMonth As Range, rptMonth As Date

Set sp = ActiveSheet.Spinners(Application.Caller)
lastVal = lastVal
rptMonth = Range("ReportMonth")
Set forMonth = Range("ReportMonth")

With sp

If Len(lastVal) = 0 Then

lastVal = lastVal - 1

ElseIf .Value > lastVal Then

forMonth.Value = DateSerial(Year(rptMonth), Month(rptMonth) + 2, 0)

Else

forMonth.Value = DateSerial(Year(rptMonth), Month(rptMonth), 0)

End If

lastVal = .Value

End With

End Sub

[Bob Phillips]

Sub Spinner1_Change()
Static lastValue As Variant
Dim spinner As MSForms.Control
Dim linkedCell As Range
Dim dir As String

With ActiveSheet

dir = "Up"
Set linkedCell = .Range(.Spinners(Application.Caller).linkedCell)
If Not IsEmpty(linkedCell.Value) Then

If linkedCell.Value < lastValue Then

dir = "Down"
End If
End If

lastValue = linkedCell.Value
End With

MsgBox dir
End Sub

[Opv]

Thank you.

[snb]

Private spin
Sub Spinner1_Change()
MsgBox IIf(Sheets(1).Spinners(1).Value > spin, "up", "down")
spin = Sheets(1).Spinners(1).Value
End Sub

[Opv]

Thanks. I love the brevity.

[snb]

In that case:


Private spin
Sub Spinner1_Change()
x = Sheets(1).Spinners(Application.Caller).Value
MsgBox IIf(x > spin, "up", "down")
spin = x
End Sub

[Opv]

Thanks again!

[Opv]

In that case:


Private spin
Sub Spinner1_Change()
x = Sheets(1).Spinners(Application.Caller).Value
MsgBox IIf(x > spin, "up", "down")
spin = x
End Sub


I've tried with no success. How would that need to be modified to, for example, call SubA if Up is clicked or SubB if Down is clicked? (P.S. I'm editing here to clarify what might not have been clear in my original post. My objective is not to have to be tied to the internal controls of the spinner but, rather, to just use the up down buttons, so I am looking for a way to bypass the controls and simply detect up or down so I can call the appropriate Subs depending on which arrow is clicked.)

[Opv]

On second thought, I can get it to work in a crude sort of way by resetting the Sheets(1).Spinners(1).value to 1 at the end of the Sub. That way I can test whether "spin" is 2 or 0 and call the appropriate Sub accordingly.


Sub Spinner1_Change()

If Sheets(1).Spinners(1).Value = 2 Then
MsgBox "Going Up"
Else: MsgBox "Going Down"
End If

Sheets(1).Spinners(1).Value = 1

End Sub


Please let me know if there is a better way to accomplish the above objective.

[Bob Phillips]

If you are not interested in the functionality of the control, why use them at all. Get yourself an up arrow image, and a down arrow image, and assign separate macros to them.

[Opv]

If you are not interested in the functionality of the control, why use them at all. Get yourself an up arrow image, and a down arrow image, and assign separate macros to them.

That's a valid question, one in fact for which there is likely no good answer. I guess I just like the looks of the spinner button better than having to use two separate buttons.

[Bob Phillips]

If you butt them up against each other, they look like a spinbutton.

[Opv]

If you butt them up against each other, they look like a spinbutton.

Yeah, I had already employed that option but was just exploring the possibilities. If forcing the spinner button to reset and testing for two known spinner values is less efficient, perhaps I'll forgo the notion and continue using the separate buttons. Thx.