VBA Excel - topOffset after scrolling the buttons & both direction scroll not possible

Question

hello I have following code, so I can scroll through my buttons in my Sheet.

Private Sub Scrollbar1_Change()
    Dim btn As Button
    Dim scrollValue As Integer
    
    
    scrollValue = 10
    
    
    For Each btn In Me.buttons
        btn.Top = btn.Top - scrollValue
    Next btn
End Sub

I have 2 problem.

1. Scrolling only possible in one direction
2. I want a topOffset of 10 to my excel (see Picture 1) but it doesnt work (see picture 2)

The video shows how i want it. I did it in a userform but cant implement it in sheet

After Answer the 1. Button isnt vissible anymore and after setting gap between buttons 0 the sorting is broken:

Answer 1

Set ScrollBar Properties

• Follow below steps to change properties

• Scrollbar1_Change event code

Option Explicit
Private Sub Scrollbar1_Change()
    Dim btn As Button, i As Long
    Dim iLoc As Long, iTop As Long
    Const TOP_GAP = 10
    Const BTN_GAP = 3
    If Me.ScrollBar1.Value <> 0 Then
        Application.ScreenUpdating = False
        For i = 1 To Me.Buttons.Count
            Set btn = Me.Buttons(i)
            iLoc = btn.Top + Me.ScrollBar1.Value
            iTop = TOP_GAP + (btn.Height + BTN_GAP) * (i - 1)
            btn.Top = IIf(iLoc > iTop, iLoc, iTop)
        Next i
        Application.ScreenUpdating = True
        Me.ScrollBar1.Value = 0
    End If
End Sub

---

Update:

• Change properties of ScrollBar :

Property	Value
Max	100
Min	0
SmallChange	1
LargetChange	1
Value	0

Option Explicit
Private Sub Scrollbar1_Change()
    Dim btn As Button, i As Long
    Dim iLoc As Long, iTop As Long
    Const TOP_GAP = 10
    Const BTN_GAP = 3
    Application.ScreenUpdating = False
    For i = 1 To Me.Buttons.Count
        Set btn = Me.Buttons(i)
        btn.Top = (btn.Height + BTN_GAP) * (i - Me.ScrollBar1.Value - 1) + TOP_GAP
        btn.Visible = (btn.Top > TOP_GAP)
    Next i
    Application.ScreenUpdating = True
End Sub

---

Update:

Option Explicit
Private Sub ScrollBar1_Change()
    Dim btn As Button, i As Long, j As Long
    Dim iLoc As Long, iTop As Long, iVal As Long
    Const TOP_GAP = 10
    Const BTN_GAP = 0.5
    Application.ScreenUpdating = False
    iVal = Me.ScrollBar1.Value
    For i = 1 To Me.Buttons.Count
        Set btn = Me.Buttons(i)
        If i > iVal Then
            If i = 1 Or i = iVal + 1 Then
                iTop = TOP_GAP
            Else
                With Me.Buttons(i - 1)
                    iTop = .Top + .Height + BTN_GAP
                End With
            End If
            btn.Top = iTop
        End If
        btn.Visible = (i > iVal)
    Next i
    Application.ScreenUpdating = True
End Sub