How to disable or limit scrolling in panes of a split worksheet

user_125244 0
Viewed 778

I'm Using Excel 2013 on Windows 10

If I split the worksheet into 4 panels from $G$4 each of the 4 panels.

I tried 

sub Worksheet_Activate()
            With ActiveWindow
                .FreezePanes = False        ' Remove previous settings
                .SplitColumn = 7                 ' $G
                 .SplitRow = 4                      ' $4
                 .FreezePanes = True            ' Use the settings
            End With
            Me.ScrollArea = "$G$4:$X$200"
end sub

but that's just a first step

In particulat I want a) to disable vertical scrolling of the first 3 rows in the upper left and upper right panel b) to disable horizontal scrolling in the upper left an lower left panel c) NOT to be able to scroll up showing the first 3 rows in the lower left and lower right panel d) NOT to be able to scroll horizontal into the first 6 columns (A to F) using the lower right scrollbar

How I can I achieve this with VBA?

freeze vba excel scrollbar split
1 Answers

I discovered how to do it by using a combination of .split=true and .freeze=true

Without .freeze=true I have 3 scrollbars and the user still will be able to scroll (only the maximum slider). But if I use .freeze=true just 1 slider remains: the one at the lower right.


Private Sub Worksheet_Activate()
    Dim rng as Range: set rng = Range('$B$4:$BF$63')     ' Note: there are 2 rows used above and 1 row below
    Me.ScrollArea = ""                      ' Clear the ScrollArea of the worksheet
    With ActiveWindow                       '  See https://docs.microsoft.com/en-us/office/vba/api/excel.window.split   
       colSplit = 6     '  Actually: some code that will idenfify where I want to split --> colSplit

       .FreezePanes = False                 '  Necessary: removes the current Panes (if any)
       .Split = False                       '  Necessary: removes the current Split (if any)
        .ScrollRow = rng.Row - 2            '  Show the 2 rows used above in the upper panes
        .ScrollColumn = rng.Column          '  Show the left column of the range  
        .SplitColumn = colSplit             '  The last column in the left panes           ' 
        .SplitRow = rng.Row - 2              '  The first row I want to see in the upper panes
        .FreezePanes = True                  '  Remove the scrollbars for the upper panes and the lower left pane
     End With

     'rng.Cells(1,colSpilt+1)  makes sure that no column of the lower left pane can be scrolled into
     Set rng = Range(rng.Cells(1, colSplit + 1).Address & ":" & rng.Cells(rng.Rows.count + 1, rng.Columns.count).Address)
     Me.ScrollArea = rng.Address(True, True, xlA1)   ' Set the ScrollArea of the worksheet --> only at the lower right pane

End Sub
user_1252440
Related