Assigning SpinUp and SpinDown Macros to a Spinner-CodePudding

I have a worksheet form spinner that needs to increment a cell value from 1 to 100. The SpinUp and SpinDown event procedures are housed in the Sheet1 VBA object, but I can only select one in the Assign Macro menu. This means the spinner can only increment up or down, irrespective of where I click.

How do I combine the two so the spinner works correctly?

Please don't suggest using an ActiveX spinner. I have done this successfully, but it has compatibility issues with Mac Excel versions so want to avoid it.

My spinner is called Spinner1 and the code in Sheet1 is:

Sub Spinner1_SpinUp()
    With Range("A2")
        .Value = WorksheetFunction.Min(100, .Value   1)
    End With
End Sub

Sub Spinner1_SpinDown()
    With Range("A2")
        .Value = WorksheetFunction.Max(1, .Value - 1)
    End With
End Sub

CodePudding user response：

Private lngPrevVal As Long

Sub Spinner1_Change()

Dim s As Spinner
Set s = Sheets("sheet1").Shapes("Spinner 1").OLEFormat.Object

    If s.Value > lngPrevVal Then
        Sheets("sheet1").Range("a1").Value = "Up"
    Else
        Sheets("sheet1").Range("a1").Value = "Down"
    End If

lngPrevVal = s.Value

End Sub