ActiveX SpinButton - Change the Value

[Papa_Don]

Hi group,

I'm using a SpinButton to move up and down rows of a table to return information. When it is clicked, I have the row value being returned to cell "G5". One of my goals was to allow the user to skip around should they need to. I'd like to allow them to update cell "G5" by manually typing in a row number and have the SpinButton go to that row. Is this something I can do?

In advance, thanks for your thoughts and ideas.

Don

 

[Roderick_E]

why not:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celljump As String
If Target.Address = "$G$5" Then
If IsNumeric(Range("G5")) Then
celljump = "A" & Range("G5")
Range(celljump).Select
End If
End If
End Sub

 

[Papa_Don]

Roderick_E,

Thanks for the reply. I tried the "Worksheet_Change" event with no success. But you got me to thinking about a different way to phrase the question. From that I learned there is a "SpinButton_SpinUp" and "SpinButton_SpinDown" event. I played with that for a few minutes and ended up writing the following code to fix my issue:

Private Sub SpinButton2_SpinDown()

With Range("G5")
.Value = WorksheetFunction.Max(1, .Value - 1)
End With
Range("J11:K13,M11:M13,K14,K19:L19,K20,M21,J23:N23,J25:N25,J27:N27,J29:N29,J31:M31,I37,J37,K37,L37,P19:S31").Select
Range("P19").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("K19").Select
Range("J11").Value = Range("C11").Value
Range("J12").Value = Range("C12").Value
Range("M11").Value = Range("F11").Value
Range("M13").Value = Range("F13").Value
Range("K14").Value = Range("D14").Value
Range("I37").Value = Range("B37").Value
Range("J37").Value = Range("C37").Value
Range("K37").Value = Range("D37").Value
Range("L37").Value = Range("E37").Value
cbxUpdateSeason.Value = False
cbxNewRatePlan.Value = False
Range("K19").Select


End Sub

And

Private Sub SpinButton2_SpinUp()

With Range("G5")
.Value = WorksheetFunction.Min(2000, .Value + 1)
End With
Range("J11:K13,M11:M13,K14,K19:L19,K20,M21,J23:N23,J25:N25,J27:N27,J29:N29,J31:M31,I37,J37,K37,L37,P19:S31").Select
Range("P19").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("K19").Select
Range("J11").Value = Range("C11").Value
Range("J12").Value = Range("C12").Value
Range("M11").Value = Range("F11").Value
Range("M13").Value = Range("F13").Value
Range("K14").Value = Range("D14").Value
Range("I37").Value = Range("B37").Value
Range("J37").Value = Range("C37").Value
Range("K37").Value = Range("D37").Value
Range("L37").Value = Range("E37").Value
cbxUpdateSeason.Value = False
cbxNewRatePlan.Value = False
Range("K19").Select

End Sub

Again, thanks for your help. Your response truly helped me discover the real answer.

Don