Kapat

Scroll Worksheet Using Spin Buttons

excel  pairing spin button with textbox 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.

excel scroll sheet

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

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. excel scroll sheet using spin button

VBA codes “Go” button : excel select row

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. excel scroll with spin button

📥 Download sample Excel template