The normal procedures for making and saving changes in the PERSONAL.XLS file is to:
Unhide the file
Windows Unhide (in the menu)
Make the changes
Hide the file
Windows Hide (in the menu)
Exit Excel, responding Yes to saving the changes
You may find it irritating to exit and reopen Excel just to save the changes.You may choose not to exit Excel immediately and continue your work. When you finish your work session and exit Excel, you will get the Save Changes dialog box. The risk in this approach is losing your changes in the event of Excel or Windows crashing. Or you may exit Excel later, and inadvertently click on No to saving the the changes.
Using Excel’s Save/Save As menu commands only allows you save the active workbook, and since the PERSONAL.XLS file is hidden, it is not active. Most of the related VBA commands work the same way.
The following macro will allow you to hide the file and save the (hidden) file within the same procedure:
Sub HideAndSave()
ThisWorkbook.Windows(1).Visible = False
ThisWorkbook.Save End Sub
This macro assumes that the PERSONAL.XLS workbook already exists as it is saved with the existing file name. This macro should be stored in the PERSONAL.XLS workbook.
About The Author: Jim Colville
More posts by Jim Colville