XL4: Workbook Disappears when Using FORMULA.GOTO()

ID: Q99701

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a
Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS

In Microsoft Excel 4.0, a workbook may disappear from the workspace if you are using the FORMULA.GOTO() macro function. This behavior usually occurs when the FORMULA.GOTO() function is used to go to a defined range on a worksheet in a second workbook.

WORKAROUND

To work around this situation, switch to the second workbook sheet and select the range of cells with another function such as the WORKBOOK.ACTIVATE() function.

For example, use the following macro:


   =ACTIVATE("B.XLW")
   =WORKBOOK.ACTIVATE("[B.XLW]Sheet1",FALSE)
   =FORMULA.GOTO("test")
   =RETURN()

Unlike the macro example shown below, the macro above does not go directly from the Workbook Contents page of the first workbook (A.XLW) to the named range on the second workbook (B.XLW). The first line of the macro activates the second workbook, B.XLW. The second line of the macro activates Sheet1 in workbook, B.XLW. The third line selects the named range, "test", and Sheet1.

STATUS

This behavior does not occur in later versions of Microsoft Excel.

MORE INFORMATION

The following example describes a situation in which this behavior will occur. At the end of this example, a macro example is given to show how to avoid this problem.

To demonstrate the behavior described in this article, follow these steps:

Open two workbooks (such as, A.XLW and B.XLW).

Create a sheet in B.XLW called Sheet1 and define a name on the sheet called "test" (without the quotation marks).

In a new macro sheet, type the following macro:


      =FORMULA.GOTO([B.XLW]Sheet1!test)
      =RETURN()

Click Arrange on the Window menu and then click Tiled. Click OK. This displays the two Workbook Contents Pages and the macro sheet. Make sure the Workbook Contents pages for both workbooks are displayed.

With A.XLW selected as the active window, click Run on the Macro menu to start the macro you created in step 2. Select the name of the macro and click OK.

When you run the macro, it selects the range "test" that is defined on Sheet1 in B.XLW. The workbook, A.XLW, will disappear completely, leaving you with three windows: one containing Sheet1 on B.XLW, one containing the Workbook Contents page of B.XLW, and the other containing the macro sheet.

If you click Unhide on the Window menu, A.XLW does not appear in the list. Also, if you click the Window menu, A.XLW does not show at the bottom as an open file. However, if you click Open on the File menu and select A.XLW, Microsoft Excel asks you if you want to "Revert to Saved 'A.XLW'?", as if the workbook is currently open. At this point you can either click Cancel or click OK.

If you try to click A.XLW in the list of four recently opened files on the File menu, Microsoft Excel will not open the file and after doing this none of the sheets on the workspace are active. Also, if you exit Microsoft Excel, you will be asked if you want changes in A.XLW saved, meaning it is still open somewhere.

REFERENCES

"Function Reference," version 4.0, pages 4, 172, 460-461

Additional query words: XL4 4.00a closes close gone removed remove

Keywords : xlformula
Version : WINDOWS:4.0,4.0a; MACINTOSH:4.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb