Going Home with Frozen Panes

I like to split a worksheet and freeze the panes so I can see the row and column headings when I scroll. Like me, I’m sure you’ve done it a million times. In that state, the Ctrl+Home shortcut will take you to the intersection of the split panes rather than A1 (where it takes you if you don’t have frozen panes). Most of the time that’s just fine with me. Sometimes, however, I have some header information that drives the worksheet like a dropdown and I’d like Ctrl+Home to go to A1 – maybe because it’s closer to the dropdown or maybe because it just feels right.

I would rather go to A1 than C5. But really, I want to go to B2. In my UIHelpers.xlsm add-in (that’s a Personal Macro Workbook for you if you haven’t converted yours to an add-in yet), I put the following lines in the Auto_Open and Auto_Close procedures.

Application.OnKey "^{HOME}", "FrozenHome"
Application.OnKey "^{HOME}"

1 2	Application.OnKey "^{HOME}", "FrozenHome" Application.OnKey "^{HOME}"

That hooks up the keystroke to a procedure called FrozenHome. I have a sneaking suspicion that hijacking Ctrl+Home is going to have some unintended consequences, but I can’t think of what they might be. The FrozenHome proc looks like this.

Sub FrozenHome()
    
    Dim rSplit As Range
    Dim rHome As Range
    
    On Error Resume Next
        Set rHome = ActiveSheet.Range("rngHome")
    On Error GoTo 0
    
    With ActiveWindow
        Set rSplit = ActiveSheet.Cells(.SplitRow + 1, .SplitColumn + 1)
    End With
    
    If ActiveCell.Address = rSplit.Address Then
        If rHome Is Nothing Then
            ActiveSheet.Cells(1, 1).Select
        Else
            rHome.Select
        End If
    Else
        rSplit.Select
    End If
    
End Sub

Sub FrozenHome()

Dim rSplit As Range

Dim rHome As Range

On Error Resume Next

Set rHome = ActiveSheet.Range("rngHome")

On Error GoTo 0

With ActiveWindow

Set rSplit = ActiveSheet.Cells(.SplitRow + 1, .SplitColumn + 1)

End With

If ActiveCell.Address = rSplit.Address Then

If rHome Is Nothing Then

ActiveSheet.Cells(1, 1).Select

Else

rHome.Select

End If

Else

rSplit.Select

End If

End Sub

This will cause Ctrl+Home to toggle between the split cell and A1. If the selection is anywhere in the worksheet that’s not the split cell, it selects the split cell. If the selection is already at the split cell, then it select A1. Except that I had to fancy that part up a bit. If I have defined a range named rngHome, it will select that instead of A1. In the screen shot above I defined B2 as rngHome so that pressing Ctrl+Home twice will take me to the validation box.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Leave a Reply