Excel VBA Scroll bar which shift sheet left or rig

问题:

Request: VBA code for mini scroll bar which shift sheet left or right using VBA or ActiveX scroll bar?

I have created a trainer skills matrix which has trainer names listed down the side (in cells 'B7' through 'B86') and Skill disciplines listed along the top (in cells E6 through 'AJ6'). Where the trainers name and a skill intersect I have a dropdown list stating ‘Y’ for has skill, ‘N’ does not skill and ‘n\a’. if the skill is not appropriate for that trainer. I have frozen the header rows (in cells E6 to 'AJ6') and trainer names (in cells 'B7' through 'B86') for easy cross reference

I would like to add a 'mini' horizontal scroll bar (located in cell A4:B4) which would behaviour exactly like the main excel sheet scroll bar (i.e moving the sheet left or right). I have tried the ActiveX scroll bar and ‘Googled’ solution for over 4hrs with no luck. Could an expert VBA programmer or Excel guru suggest a solution or spost some sample code? Cheers!!

Screenshot of my Trainer Skills Matrix

回答1:

For an ActiveX scrollbar:

'in the sheet code module
Private Sub ScrollBar1_Change()
    ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
End Sub

Private Sub ScrollBar1_Scroll()
    ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
End Sub

Set the scrollbar min to 1 and the max to however many columns you have...

EDIT: if you wanted the scrollbar to move along with the columns then you could do something like this in the Change event (but if you add it to the Scroll event it's going to produce some odd results:

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

BUT - if you then manually move the sheet (via the arrow keys etc) then the scrollbar will move out of view, and there's no sheet_scroll event to catch to use to re-position the scrollbar.

回答2:

please put following code on sheet selection change

Me.ScrollBar1.Value = ActiveWindow.ScrollColumn - 4

please adjust the columns which you want to scroll as fitt to active window then adjust the max scroll value for scrollbar1.