Microsoft KB Archive/107385

= Microsoft Knowledge Base =

XL: WORKBOOK.ACTIVATE Returns Error If Sheet_Name Omitted
Last reviewed: March 27, 1997

Article ID: Q107385

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS   | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Macintosh, versions 5.0, 5.0a

SYMPTOMS
In the versions of Microsoft Excel mentioned above, if you do not specify the sheet_name argument when you use the WORKBOOK.ACTIVATE command in a Microsoft Excel version 4.0 macro, the command will return a macro error.

CAUSE
In earlier versions of Microsoft Excel, you can omit the first argument of the WORKBOOK.ACTIVATE command to activate the workbook contents screen. Because Microsoft Excel versions 5.0 and higher, do not have a workbook contents screen, sheet_name is a required argument.

The workbook contents screen has been replaced with tabs located along the bottom of the Microsoft Excel application window. There is a tab for each worksheet, module, Microsoft Excel 4.0 macro, and chart in the workbook.

WORKAROUND
Avoid using a macro command to activate a workbook contents window. You can perform the desired action on a workbook in Microsoft Excel versions 5.0 and higher, without this command.

For example, the following macro in Microsoft Excel version 4.0 activates the workbook contents window in the current workbook and protects it.

A1: Protect_Contents A2: =WORKBOOK.ACTIVATE(,FALSE) A3: =PROTECT.DOCUMENT(TRUE,FALSE,,FALSE) A4: =RETURN If you run this same macro in Microsoft Excel versions 5.0 or higher, a macro error occurs at cell A2 because the second argument in this function is not supported. To accomplish the same action in Microsoft Excel versions 5.0 or higher using a macro, use the following examples:

Visual Basic Macro
Sub Protect_Contents ActiveWorkbook.Protect Structure:=True, Windows:=False End Sub Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Microsoft Excel Version 4.0 Macro
A1: Protect_Contents A2: =WORKBOOK.PROTECT(TRUE,FALSE) A3: =RETURN 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 in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements.