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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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.
Posting code? Use <pre> tags for VBA and <code> tags for inline.