☰

Excel 中是否有后退/前进功能，可以让您在单元格之间前进和后退？

tag-icon • tag-icon microsoft-excel
Excel 中是否有后退/前进功能，可以让您在单元格之间前进和后退？

我希望能够在我编辑过或输入过的单元格之间来回跳转Name Box。

我已经将前进和后退按钮添加到快速访问工具栏，但它们仅在我单击链接时才起作用。

答案1

这是一个很有趣的想法，我也想要它。我建议把代码放在你的个人宏工作簿因此它将作用于您的所有文件，但您当然不必这样做。

您可以使用 VBA 将键绑定到宏。以下是将Ctrl+ Alt+绑定NumPad#到保存单元格位置并将Ctrl+NumPad#绑定到激活单元格位置的代码。这是受 RTS 游戏的启发，在 RTS 游戏中，您可以使用 CTRL 和数字键来创建和选择单位组。不过，Excel 已经将 CTRL + 数字行用作各种快捷方式，因此我将它们绑定到数字键盘。它设置为使用数字键盘上的 0 - 9 键，因此您可以保存/调用最多 10 个范围。请注意，这是一个范围单元格，这样您就可以根据需要选择一个、一组或多个不同的单元格。我还没有测试过如果有多个会发生什么床单但我怀疑它是否会对此感到高兴。

此代码进入ThisWorkbook模块。由于事件，工作簿打开时会触发此代码Workbook_Open()。它将创建一个隐藏的工作表来存储已保存的单元格，然后创建所有键绑定。

Private Sub Workbook_Open()
    
    ' Create a hidden worksheet to store the saved cells in this workbook
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("SavedCells")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = Worksheets.Add
        ws.Name = "SavedCells"
        ws.Visible = xlSheetVeryHidden
    End If
    
    ' Create key bindings to go to the cells
    Application.OnKey "^{096}", "'gotoCell 0'"  ' CTRL + NumPad0
    Application.OnKey "^{097}", "'gotoCell 1'"  ' CTRL + NumPad1
    Application.OnKey "^{098}", "'gotoCell 2'"  ' CTRL + NumPad2
    Application.OnKey "^{099}", "'gotoCell 3'"  ' CTRL + NumPad3
    Application.OnKey "^{100}", "'gotoCell 4'"  ' CTRL + NumPad4
    Application.OnKey "^{101}", "'gotoCell 5'"  ' CTRL + NumPad5
    Application.OnKey "^{102}", "'gotoCell 6'"  ' CTRL + NumPad6
    Application.OnKey "^{103}", "'gotoCell 7'"  ' CTRL + NumPad7
    Application.OnKey "^{104}", "'gotoCell 8'"  ' CTRL + NumPad8
    Application.OnKey "^{105}", "'gotoCell 9'"  ' CTRL + NumPad9
    
    ' Create key bindings to save the cells
    Application.OnKey "^%{096}", "'saveCell 0'"  ' CTRL + ALT + NumPad0
    Application.OnKey "^%{097}", "'saveCell 1'"  ' CTRL + ALT + NumPad1
    Application.OnKey "^%{098}", "'saveCell 2'"  ' CTRL + ALT + NumPad2
    Application.OnKey "^%{099}", "'saveCell 3'"  ' CTRL + ALT + NumPad3
    Application.OnKey "^%{100}", "'saveCell 4'"  ' CTRL + ALT + NumPad4
    Application.OnKey "^%{101}", "'saveCell 5'"  ' CTRL + ALT + NumPad5
    Application.OnKey "^%{102}", "'saveCell 6'"  ' CTRL + ALT + NumPad6
    Application.OnKey "^%{103}", "'saveCell 7'"  ' CTRL + ALT + NumPad7
    Application.OnKey "^%{104}", "'saveCell 8'"  ' CTRL + ALT + NumPad8
    Application.OnKey "^%{105}", "'saveCell 9'"  ' CTRL + ALT + NumPad9
    
End Sub

此代码进入同一文件中的一个模块。它负责保存和调用单元格。出于我自己的目的，我注释掉了消息框，因为我写了这个东西，所以我知道如果它不起作用会发生什么。您可以随意保留它们或将它们删除。

Function gotoCell(cellNumber As Integer)

    ' Convert the cell ID number to a row number
    Dim r As Integer
    r = cellNumber + 1
    
    ' Try to recover the saved cell
    Dim tempRange As Range
    Dim combinedText As String
    On Error Resume Next
        With Worksheets("SavedCells")
            Set tempRange = Workbooks(.Cells(r, 1).Value).Worksheets(.Cells(r, 2).Value).Range(.Cells(r, 3).Value)
            combinedText = .Cells(r, 1) & .Cells(r, 2) & .Cells(r, 3)
        End With
    On Error GoTo 0
    
    ' Go to the cell if it was found
    If Len(combinedText) = 0 Then
        MsgBox "Cell " & cellNumber & " has not been saved."
    ElseIf tempRange Is Nothing Then
        MsgBox "Cell " & cellNumber & " could not be found." & vbNewLine & "It is possible that the workbok was closed or a worksheet was renamed."
    Else
        Application.Goto tempRange
    End If
    
End Function

Function saveCell(cellNumber As Integer)
    
    ' Convert the cell ID number to a row number
    Dim r As Integer
    r = cellNumber + 1
    
    ' Try to get the currently selected cells
    Dim tempRange As Range
    On Error Resume Next
        Set tempRange = Application.Selection
    On Error GoTo 0
    
    ' Save the range if one was found
    If tempRange Is Nothing Then
        MsgBox "Could not save cell " & cellNumber & "." & vbNewLine & "Please select a cell range and try again."
    Else
        With Worksheets("SavedCells")
            .Cells(r, 1) = tempRange.Parent.Parent.Name
            .Cells(r, 2) = tempRange.Parent.Name
            .Cells(r, 3) = tempRange.Address
        End With
    End If
    
End Function

为了完整性，您可能还应该将其包含在ThisWorkbook模块中联合国关闭工作簿时绑定按键。如果代码在您的个人宏工作簿中，这应该不会有太大影响，因为通常只有在您关闭 Excel 时才会关闭，因此按键绑定无论如何都会被清除。但是，我仍然认为最好自己清理一下。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Application.OnKey "^{096}"
    Application.OnKey "^{097}"
    Application.OnKey "^{098}"
    Application.OnKey "^{099}"
    Application.OnKey "^{100}"
    Application.OnKey "^{101}"
    Application.OnKey "^{102}"
    Application.OnKey "^{103}"
    Application.OnKey "^{104}"
    Application.OnKey "^{105}"
    
    Application.OnKey "^%{096}"
    Application.OnKey "^%{097}"
    Application.OnKey "^%{098}"
    Application.OnKey "^%{099}"
    Application.OnKey "^%{100}"
    Application.OnKey "^%{101}"
    Application.OnKey "^%{102}"
    Application.OnKey "^%{103}"
    Application.OnKey "^%{104}"
    Application.OnKey "^%{105}"
    
End Sub

相关内容