Control + Home in Excel for Mac

One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.

I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.

My Journey

I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.

I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.

To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.

The Control+Home Macro

I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)


Sub GoHome()
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Make sure the active sheet is a worksheet,
' then locate the active window's scroll row and
' column, and activate that cell.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lngRow As Long
Dim lngCol As Long

If ActiveSheet.Type = xlWorksheet Then
lngRow = ActiveWindow.ScrollRow
lngCol = ActiveWindow.ScrollColumn
Cells(lngRow, lngCol).Activate
End If
End Sub

The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!

I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.

However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.

How I Created the Control+Home Keyboard Shortcut on my Mac

Here are the steps I took, with a few false starts along the way.

Open Excel 2011 and choose Tools > Macro > Record New Macro…

Type a name for the macro. I used GoHome.

Enter a Shortcut key. (I used h, which didn’t work so I changed to g as you’ll see below.)

Where you see Store macro in: click the drop-down and select Personal Macro Workbook.

Click OK.

I got a warning that the keyboard shortcut Option+Command+h was reserved.

So I chose g instead and clicked OK.

Next choose Tools > Macros > Stop Recording. This will effectively end the macro recording without recording anything.

Choose Tools > Macro > Macros…

Click the Step Into button on the Macro dialog box, which will take you directly inside the macro in the VBA Editor. (Note: While this takes you directly to the macro, it also starts the macro running inside the VBA Editor.)

Click the square Reset button to stop the macro program execution. (Note: If you know how to navigate the VBA editor, you can skip this last step and choose Tools > Macro > Visual Basic Editor and then locate the macro.

Select the entire GoHome code from this article. Or click this link and copy from a new window.

Copy the text,

Switch back to the Excel VBA editor,

Select the entire GoHome subroutine, and

Paste the code.

Close the VBA editor by choosing Excel > Close and Return to Microsoft Excel.

Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.

Choose Excel > Quit Excel and the following dialog box will appear.

Click the Save button when asked, “Do you want to save the changes you made to the Personal Macro Workbook?”

Run the GoHome Macro

Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.

If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.

Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.

The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.