☰

Excel 快捷方式展开/折叠整个组大纲级别

tag-icon • tag-icon microsoft-excel keyboard-shortcuts microsoft-excel-2019
Excel 快捷方式展开/折叠整个组大纲级别

我正在尝试找出 Excel 中是否有快捷键组合来展开和折叠整个组大纲级别。这相当于用鼠标单击窗口左上角的 1、2、3、... 按钮：

Excel 中的组大纲级别按钮

我知道您可以使用ALT+ a+ j/ ALT+ a+展开和折叠单个部分h，但我的一些电子表格包含许多分组部分，并且经常发现自己想将它们全部折叠到给定级别。理想情况下，这不需要我伸手去拿鼠标...

我已经在 Google 上搜索过这个问题，但要么找不到，要么超出了我的 Google 技能

答案1

我能想到的唯一方法是使用Ctrl+ A，然后

ALT+ A+ J/ ALT++A​H

您还可以将这些操作录制为宏，并为宏分配键盘快捷键

答案2

受到彼得的评论的启发，我全力以赴，编写了一个可以扩展到任意数字输入级别的宏，这比我想象的要难！

然后在我的插件启动的某个地方，我得到了这一行Application.OnKey Key:="^+r", Procedure:="OutlineRowLevels"。这意味着我现在可以按“快捷键”：CTRL++ SHIFT，R然后按数字[ 1- 9]来展开/折叠到所需的级别。

我也对列做过类似的事情，但没有看到用几乎相同的代码发送垃圾邮件的意义。

#If Win64 Then
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" _
        (ByVal vKey As Long) As Integer
#Else
    Private Declare Function GetAsyncKeyState Lib "user32" _
        (ByVal vKey As Long) As Integer
#End If

Private Const VK_1 = &H31 '1 key
Private Const VK_2 = &H32 '2 key
Private Const VK_3 = &H33 '3 key
Private Const VK_4 = &H34 '4 key
Private Const VK_5 = &H35 '5 key
Private Const VK_6 = &H36 '6 key
Private Const VK_7 = &H37 '7 key
Private Const VK_8 = &H38 '8 key
Private Const VK_9 = &H39 '9 key
Private Const VK_ESCAPE = &H1B 'ESC key

Public Sub OutlineRowLevels()

Dim dTime As Date

    ' Record time 3 seconds from now
    dTime = DateAdd("s", 3, Time)

    ' Disable number keys or Excel will start editing
    Application.OnKey "1", ""
    Application.OnKey "2", ""
    Application.OnKey "3", ""
    Application.OnKey "4", ""
    Application.OnKey "5", ""
    Application.OnKey "6", ""
    Application.OnKey "7", ""
    Application.OnKey "8", ""
    Application.OnKey "9", ""

    ' Exit when ESC key is pressed
    ' or more than 3 seconds elapsed
    Do Until GetAsyncKeyState(VK_ESCAPE) Or (Time > dTime)

        DoEvents

        If GetAsyncKeyState(VK_1) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=1
            Exit Do
        ElseIf GetAsyncKeyState(VK_2) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=2
            Exit Do
        ElseIf GetAsyncKeyState(VK_3) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=3
            Exit Do
        ElseIf GetAsyncKeyState(VK_4) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=4
            Exit Do
        ElseIf GetAsyncKeyState(VK_5) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=5
            Exit Do
        ElseIf GetAsyncKeyState(VK_6) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=6
            Exit Do
        ElseIf GetAsyncKeyState(VK_7) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=7
            Exit Do
        ElseIf GetAsyncKeyState(VK_8) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=8
            Exit Do
        ElseIf GetAsyncKeyState(VK_9) Then
            ActiveSheet.Outline.ShowLevels RowLevels:=9
            Exit Do
        End If

    Loop

    ' Re-enable number keys
    ' ... or Excel will be crippled ;)
    Application.OnKey "1"
    Application.OnKey "2"
    Application.OnKey "3"
    Application.OnKey "4"
    Application.OnKey "5"
    Application.OnKey "6"
    Application.OnKey "7"
    Application.OnKey "8"
    Application.OnKey "9"

End Sub

相关内容