Macro to Open or Activate a Sheet in Microsoft Excel

ID: Q74694

The information in this article applies to:

Microsoft Excel for Windows, versions 2.0, 2.01, 2.1, 2.10c, 2.10d, 3.0, 4.0, 4.0a
Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

SUMMARY

When you activate a worksheet from a macro, the worksheet must first be opened. You will receive a "Macro Error at Cell: xx" if the sheet is closed.

The following macro will activate SHEET1.XLS if it is already open, or it will open and activate SHEET1.XLS if the sheet is closed.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

Enter the following into a macro sheet:
A1: ActivateSheet
A2: =ERROR(FALSE)
A3: =IF(ISERROR(ACTIVATE("SHEET1.XLS")),OPEN("SHEET1.XLS"))
A4: =ERROR(TRUE)
A5: =RETURN()

Define the macro by selecting cell A1. Click Define Name on the Formula menu.

The name will appear in the Name box.

Select Command in the Macro section at the bottom of the dialog box and click OK.

For information about how to do this with a Visual Basic for Applications procedure, please see the following article in the Microsoft Knowledge Base:

Q135390 XL: VB for Apps Procedure to Open or Activate a Workbook

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 135, 225, and 24
"Microsoft Excel Function Reference," version 3.0, pages 68, 125, and 135
"Microsoft Excel Functions and Macros," version 2.1, pages 57, 65-66, 274

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0

Keywords : kbmacro kbprg
Version : WINDOWS:2.0,2.01,2.1,2.10c,2.10d,3.0,4.0,4.0a; :2.2,2.21,3.0
Platform : WINDOWS
Issue type :