OVERVIEW
The Worksheet Manager displays a grid showing a selection of properties of the sheets in the workbook. A user can manipulate these properties using the user interface. If changes have been made, the Worksheet Manager shows how the workbook will be after the changes are applied.
The columns displayed in the grid are as follows:
Column |
Description |
Index |
The ordinal position of the sheet in the workbook. Note that though a sheet can be moved up and down in the display order, its index will remain the same. The "Set Order" button is used to reindex the sheets to the displayed order. |
Type |
Indicates the Excel sheet type: Worksheet, Chart, Dialog, Excel 4 Macro, Excel 4 International Macro |
Sheet Type (optional) |
This is an optional field that displays the value of a specially selected local name defined to indicate the purpose of the sheet. It is toggled using the "Display Name" button. See section Type Name for Sheets for instructions on how to use this feature. |
Sheet |
The name of the sheet. |
Protected |
Indicates if the sheet is protected. |
Calculate |
Indicates if calculation is enabled for the sheet. Altering this property can have significant consequences, so there are a variety of options in the Worksheet Manager options for restricting the use of this facility. |
Visibility |
Shows the visibility of the sheet. |
SELECTION
The selection panel is toggled by clicking the "Select" button. The following types of selection can be made:
Index |
Comma-separated lists of numbers or ranges. In a workbook with 12 sheets, the following example selects the sheets at index 1, 2, 5,6,7,8,10,11 and 12: 1,2,5-8,10- |
Type |
Worksheet, Chart, Dialog, Excel 4 Macro, Excel 4 International Macro |
Sheet Type (optional) |
One of the values selected from a list of values taken from the sheet type name as defined in the worksheets. |
Sheet |
A wildcard string. Use a question mark (?) to indicate a single character, and an asterisk (*) to indicate any number of any character. |
Protected |
Checked, unchecked or indeterminate (filled in) to ignore this property. |
Calculate |
Checked, unchecked or indeterminate (filled in) to ignore this property. |
Visibility |
Visible, Hidden, Very Hidden or blank to ignore this property. |
SORTING
The displayed order of the sheets can be sorted by up to two of index, Excel sheet type, or name, in ascending (+) or descending (-) order.
COPYING
Select the sheets that are to be copied, and click the Copy button on the Worksheet Manager's toolbar. Copies of each selected sheet will be inserted immediately after their source.
The sheets are not actually copied until after the "Apply" or "OK" button has been clicked.
INSERTING
New sheets are inserted either after (default) or before the first item in the current selection.
A new worksheet can be inserted by clicking the "Insert" button.
Alternatively, the arrow beside the "Insert" button can be clicked, revealing a variety of options including other types of sheet to insert, or the option to change where the sheets are inserted, i.e. before or after the first item in the selection.
The sheets are not actually inserted until after the "Apply" or "OK" button has been clicked.
DELETING
Select the sheets that are to be deleted and click the "Delete" button on the Worksheet Manager toolbar. A confirmation dialog box will be shown. Following confirmation, the selected sheets will be removed from the displayed list.
The sheets are not actually deleted from the workbook until the "Apply" or "OK" button has been clicked.
REORDERING
Sheets can be reordered by sorting, or by clicking the up and down arrows in the toolbar. These operations change the displayed order of the sheets, not their index values. To set the index values to the displayed order, click the "Set Order" button on the Worksheet Manager toolbar.
When the "Apply" or "OK" button has been clicked, the reordering will be applied. This can take a long time, and can also invalidate 3D references.
FIND / REPLACE (RENAMING)
The Find and Replace panel is displayed by clicking the "Find" or "Replace" buttons on the Worksheet Manager toolbar. If the panel is displayed using the "Find" button, the replacement options will be disabled.
The text to be found is entered in the "Find" combo box. Enter a new value, or select an old one from the list. Wildcards using * and ? are accepted.
When the "Replace" controls are activated, the replacement text can be entered or selected in the "Replace with" combo box.
The search can be restricted to the selected worksheets only by checking the "Search selection only" check box.
Case sensitivity can be specified with the "Match case" check box.
Once the "Find Next" button has been clicked and the first match has been found, the Worksheet Manager enters a search state. The user is able to edit the current item, pressing enter to accept the change, and click "Find Next" to move on to the next match. To get out of this state, click the "Reset" button that is located below it.
For replacement operations, click "Replace" to step through the replacements one by one, or "Replace All" to attempt all the replacements.
It is quite simple to specify a replacement that results in a set of names that Excel would not allow, e.g. replace "Sheet?" with "Sheet". In this case the Worksheet Manager will not allow the replacement.
EDITING SHEETS
The "Protected" and "Calculate" properties can be edited directly in the grid.
Other properties are edited in the "Edit" panel below the grid.
The enter/return key can be used to accept the name value.
The sheets are not actually renamed in the workbook until the "Apply" or "OK" button has been clicked.