如何禁用或限制在拆分工作表的窗格中滚动

Question

我在Windows 10 上使用Excel 2013

如果我把工作表从$G$4分成4个小组，每个小组4个。

我试过

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

但这只是的第一步

特别是我想要的a( 禁用左上角和右上角面板中前3行的垂直滚动b( 要禁用左上角和左下角面板中的水平滚动c( 无法向上滚动显示左下和右下面板中的前3行d( 无法使用右下滚动条水平滚动到前6列(A至F(

如何使用VBA实现此功能？

Answer 1

我发现了如何使用.split=true和.freeze=true 的组合来实现这一点

如果没有.freeze=true，我有3个滚动条，用户仍然可以滚动(只有最大滑块(。但如果我使用.freeze=true，只剩下一个滑块：右下角的滑块。

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://learn.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