Scroll Worksheet Using Spin Buttons

Excel Spin Button Examples

Sometimes scroll bar controls in the worksheet may not work or may not respond to mouse clicks. As a solution, we created a template where we can scroll the worksheet with spin buttons as an alternative to the scroll bar.

✔ Firstly, we created a userform on the template and added spin buttons to this userform to scroll the worksheet vertically and horizontally. ⬇

VBA codes that we add to the spin buttons to scroll the sheet horizontally and vertically : ⬇

Private Sub SpinButton2_SpinDown()
ActiveWindow.SmallScroll Down:=1        'Scroll down
    ActiveWindow.ActivateNext
    ActiveWindow.SmallScroll Down:=0
    ActiveWindow.ActivatePrevious
End Sub

Private Sub SpinButton2_SpinUp()
ActiveWindow.SmallScroll Up:=1           'Scroll up
    ActiveWindow.ActivateNext
    ActiveWindow.SmallScroll Up:=0
    ActiveWindow.ActivatePrevious
End Sub

Private Sub SpinButton3_SpinDown()
ActiveWindow.SmallScroll ToLeft:=0        'Scroll left
    ActiveWindow.ActivateNext
    ActiveWindow.SmallScroll ToLeft:=1
    ActiveWindow.ActivatePrevious
End Sub

Private Sub SpinButton3_SpinUp()
ActiveWindow.SmallScroll ToRight:=0       'Scroll right
    ActiveWindow.ActivateNext
    ActiveWindow.SmallScroll ToRight:=1
    ActiveWindow.ActivatePrevious
End Sub

✔ With the textbox and button that we added to the top of worksheet, the corresponding row to the entered number in the textbox is selected. ⬇

VBA codes “Go” button : ⬇

Private Sub CommandButton1_Click()
Dim z As Long
     If Not IsNumeric(TextBox1.Value) Then
       MsgBox "Enter Numeric Value", vbCritical, ""
       TextBox1 = Empty
       Exit Sub
    End If
    If TextBox1.Value = 0 Then
       MsgBox "Please Enter a Valid Value", vbCritical, ""
       TextBox1 = Empty
       Exit Sub
    End If

z = TextBox1.Value
ActiveSheet.Rows(z).Select
End Sub 

The task of spin button on the worksheet is to scroll worksheet by the entered amount in the textbox next to spin button. ⬇