Microsoft Knowledge Base

WINDOWS                  MACINTOSH

The information in this article applies to:

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

SUMMARY

The versions of Microsoft Excel listed above do not allow group editing across multiple workbooks. Sheets can be grouped within a workbook by selecting multiple sheet tabs in the workbook.

NOTE: In Microsoft Excel version 4.0, you have the option of performing a group edit across multiple workbooks by using the Group Edit command on the Options menu. Note also that returning to the Excel 4.0 menus will not yield the Group Edit command. This command is replaced on the Excel 4.0 menus in Microsoft Excel versions 5.0 and 7.0 by the New Menus command.

MORE INFORMATION

By grouping sheets, you can perform tasks on all of the sheets in the group simultaneously. For example, you could enter a value in cell A1 on all sheets in one operation.

To group adjacent sheets in the same workbook

Click the first sheet tab, hold down the SHIFT key, and then click the last sheet tab in the group you want to select.

To group nonadjacent sheets in the same workbook

Click the first sheet tab, hold down the CTRL key, and then click the other sheet tabs that you want in the group.

To group sheets in the different workbooks

To group sheets in different workbooks, you must use a macro to display the Microsoft Excel version 4.0 Group Edit box.

Note that it is recommended to use the Excel 5.0 method of creating a group edit, but if there is a situation that requires a group edit across more than one workbook, the procedure below will provide that functionality.

Microsoft provides examples of Visual Basic 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. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

1. To use the Microsoft Excel version 4.0 Group Edit dialog box, enter the following in a Visual Basic module sheet:

      Sub Group_Edit()
         'Brings up the built in Excel Group Edit dialog
         Application.Dialogs(xlDialogWorkgroup).Show
      End Sub
   
   

2. To run the procedure, position the cursor in the line that reads "Group_Edit()" and either press the F5 key or choose Start from the Run menu.

REFERENCES

For more information on using built in dialog boxes in a procedure do the following:

1. In a module sheet, choose Object Browser from the View menu.

2. Select the Excel Library, and then choose the Constants object.

3. Scroll the list in the Methods/Properties box until you find the constants that begin with xlDialog.