Search code examples
excelvbascrollbaractivex

Scroll excel window left to right with activex control

enter image description here

I have six sections of employee information out of which two are shown below. Below the sections I have an activex scroll bar which I want to use from left to right. This scrollbar is different from the scrollbar in excel sheet. Basically, currently I see Section 1 and Section 2. When I move this scroll from left to right, I should see section 2 and 3, once more when I move from left to right I should see section 3 and section 4,.... and so on.

How can I achieve this functionality.

I have tried using this piece of code here.

    Private Sub ScrollBar3_Change()
    Dim sc As Long
    sc = 4 + Me.ScrollBar3.Value
    ActiveWindow.ScrollColumn = sc
    Me.ScrollBar3.Left = Me.Cells(1, sc).Left
    End Sub

The scrollbar currently starts from C32 and ends at AA32. But when I click on it, the scrollbar suddenly disappears.

Thanks in advance :)

Solution

  • You should the Scrollbar.Max should equal the maximum number of moves. I would also suggest using Application.Goto Range(), True before you move the Scrollbar.

    enter image description here

    enter image description here

    Here is an easy way to ensure that everything stays in sync:

    Set the Worksheet.ScrollArea will limit the users ability to scroll

    enter image description here

    User Friendly Code

    Private Sub ScrollBar3_Change()
    Dim col As String
    col = Choose(Me.ScrollBar3.Value, "C", "N", "AB")
    Application.Goto Cells(21, col), True
    Me.ScrollBar3.Left = Columns(col).Left
End Sub

    Totalitarian Code

    Private Sub ScrollBar3_Change()
    Dim col As String
    col = Choose(Me.ScrollBar3.Value, "C", "N", "AB")
    Me.ScrollArea = ""
    Application.Goto Cells(21, col), True
    Me.ScrollArea = Cells(21, col).Address
    Me.ScrollBar3.Left = Columns(col).Left
End Sub