PRB: AutoSave Add-In May Cause Excel Worksheet to Switch Focus on Save

When you use the AutoSave Add-in (AutoSave.xla) in Excel 2000, the add-in may cause the current workbook to switch from the active sheet that you are editing to the first sheet in the workbook. This behavior occurs when the dialog box appears because the add-in is set to prompt you before you save. After you save the workbook, you are now on the wrong sheet. You must manually return to the location where you were working before the save.

The problem occurs when another program or another add-in changes the caption of the window of the active workbook. The caption does not contain a string that starts with the workbook name.

The AutoSave Add-in was written to switch focus to the active workbook by name. This problem occurs before the dialog box appears and prompts if the workbook must be auto-saved. By default, the code expects the window caption to contain the workbook name. If the caption does not contain the workbook name, Excel finds any window that is associated with the workbook and then activates that window. Typically, this is the first sheet of the workbook.

Because the problem only occurs with a process that runs outside Excel, the problem is not common.

Because the problem only occurs if the add-in is set to prompt you before a save, you can avoid the problem by changing the AutoSave Add-in settings so that you are not prompted. To do this, follow these steps in the Add-in Configuration dialog box:

1. On the Tools menu, click AutoSave.
2. Click to clear the Prompt before saving check box.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce the Problem

1. Run Excel.
   
   By default, Book1 is created.
2. On the Tools menu, click Add-Ins. Locate the AutoSave Add-in that is listed in the dialog box. Select the add-in. Click OK to load the add-in.
   
   Note You may receive a prompt to install this feature if you have not installed it already.
3. Press ALT+F11 to run the Microsoft Visual Basic for Applications (VBA) Editor. Type the following code in the window:

   Application.ActiveWindow.Caption = "MyCustomCaption"

   Press ENTER to set the caption.
4. Close the VBA Editor and then return to Excel.
   
   Make sure that the caption of the workbook is changed to MyCustomCaption.
5. On the Tools menu, click AutoSave. Set the active workbook to save every one minute. Make sure that you select the Prompt before saving option.
6. Add some data to Sheet1 in Book1. Move to Sheet2 and add more data.
7. Remain on Sheet2 and wait for the AutoSave dialog box to appear and to prompt you to save. If you click Save or Cancel, the active sheet is now Sheet1 instead of the last sheet that you were on.