Automatically Hiding the Personal Workbook

Ken is having a problem with his Personal.xls workbook. When he needs to work on it, he unhides it. If he forgets to rehide it when he is done, then the next time he starts Excel the Personal.xls workbook is immediately visible. Since it looks like a new, blank worksheet, he often starts typing in it and this messes up her Personal.xls workbook. He wonders if there is a way to automatically force Personal.xls to be hidden if he forgets to hide it manually.

There are a couple of things you need to keep in mind. First, if you are only making changes to macros in Personal.xls, you don't need to unhide the workbook to work on those macros. Instead, display the VBA editor and use the object browser to make sure you are working on the macros in the Personal.xls workbook. When you are done editing the macros, you can save them without ever needing to make the workbook visible.

If this still doesn't work for you—perhaps you have some other reason to make Personal.xls visible—then you could make some sort of editing change to the first worksheet in the workbook. For instance, place the text "THIS IS PERSONAL" into cell A1 of the workbook. Do something to make it stand out (bold, colors, flashing, etc.), and you will never again miss that you are working in the Personal.xls workbook when you first start Excel.

If you want a macro approach to make sure that the workbook is hidden, then you could add the following code to the ThisWorkbook object for Personal.xls:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Windows("PERSONAL.XLS").Visible = False
    ThisWorkbook.Save
End Sub

The macro is executed just before the workbook is closed (when happens when Excel is exited). It hides the workbook and then saves it. That way, the next time you start Excel, Personal.xls will be automatically hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3371) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Hiding the Personal Workbook.