gpt4 book ai didi

excel - 如何在拆分工作表的 Pane 中禁用或限制滚动

转载 作者:行者123 更新时间:2023-12-04 19:50:28 25 4
gpt4 key购买 nike

我在 Windows 10 上使用 Excel 2013

如果我将工作表分成 4 个面板,每个面板 $G$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 实现此目的?

最佳答案

我发现了如何通过结合使用 .split=true 和 .freeze=true 来做到这一点

没有 .freeze=true 我有 3 个滚动条,用户仍然可以滚动(只有最大的 slider )。但是,如果我使用 .freeze=true,则只剩下 1 个 slider :右下角的 slider 。


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

关于excel - 如何在拆分工作表的 Pane 中禁用或限制滚动,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61711800/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com