Name Manager Action Buttons

Hide, Unhide Buttons

Hides or un-hides the selected name(s). With one name selected, only the button that applies is enabled, with more than one name selected, both buttons are enabled. Tables cannot be hidden.

Add Button

Opens the Add Name form: see below.

Delete Button

Deletes the selected names. Tables cannot be deleted by Name Manager.

List Button

Creates a new worksheet in the workbook with a listing of names in the workbook with all of their properties. You will be asked whether or not to use the filters set up in the dialog to filter the names to be listed. Tables are not included.

Pickup Button

This button is only enabled when the Name Manager finds a worksheet named according to this scheme: “Names yyyy-mmm-dd”. A sheet by that name will be created automatically by clicking the “List” button.

The button picks up the selected names from the names listing that has been created by the List Button. You can edit the names list (not the names themselves, but their refersto property and their visible property), you can even add new names to this listing (be sure to include the refersto and visible property, the other properties can be omitted). All selected rows of this listing will be picked up.

Note that the values of the cells will be used to create the names, NOT the formulas in the cells!

Existing names are changed to reflect the listing, new names are added.

You can use the List and Pickup buttons to transfer names between workbooks by copying the Names worksheet into a new workbook and then using the Pickup button.

You can also do multiple edits on the Refersto formulas on the Names sheet and then reload the changed definitions back into the workbook using the Pickup button.

Localize Button

Makes the selected names local to a worksheet.

This Button is enabled when either a global name is selected or multiple names are selected.

Three cases are possible here:

A name refers to one sheet: The name is localized to that sheet automatically

A name refers to more than just one sheet: You are prompted to choose one sheet to localize that name to.

A name refers to no sheet: The name will be localized to the active worksheet.

Please note, that only the name itself will be changed, not the formulas in your worksheets.

If you have the confirmation checkbox checked, you will be prompted whether or not to delete the global name(s). If confirmation is switched off, the global name(s) will NOT be deleted.

Tables cannot be local to a worksheet and are ignored by this command

Globalize Button

The Button is enabled when either a local name is selected or one is in multi-mode. Local names will be made global by removing the sheet name prefix.

If you have the confirmation checkbox checked, you will be prompted whether or not to delete the local name(s). If confirmation is switched off, the local name(s) will NOT be deleted.

Note: only the name itself will be changed, not the formulas in your worksheets.

Tables cannot be local to a worksheet and are ignored by this command

Evaluate Button

Click this Button to get a scrollable textbox with the value(s) the first selected name/table evaluates to.

Analyze Name Button

When one name is highlighted in the main window, you can press the Analyze button to show Formula Explorer Pro for the selected Name.

All Explorer Pro functions are available except for Edit Mode.

In the Explorer Pro form the name of the Name being explored is shown at the top left, and the home cell textbox is shown with a light blue background to indicate you are exploring a Named Formula.

Highlight Button

Use this button to highlight the ranges referred to by all names as they are currently shown in the Name Manager’s main screen (names not shown due to filtering will not be highlighted).

After clicking this button you will first be prompted whether you wish to remove any existing highlights. The Name Manager will show a message stating all names it had trouble highlighting. Especially names referring to a 3D range and names that have a formula may be impossible to highlight.

Clear Button

Clears all highlights produced with the Highlight button.

Is Used ? Button

Press this button to let Name Manager search for uses of the first selected name. A screen will be shown indicated all objects and cells where the name is in use. Click on one of the items to have the object selected. Tables are ignored by Is Used. 

Refresh Button

In XL 2003 and up, the Name manager’s form is shown Modeless and you can switch to Excel without closing the name manager’s window. To make sure the name manager is up to date, press this button after doing edits in your workbook while the name manager is showing.

GoTo button 

Clicking this button with a Name selected (or double-clicking a Name in the Names Listbox) will cause Excel to switch to the workbook and worksheet and referred-to range of the Name.

GoTo will have no effect for Names such as Names containing Formulas, or referring to ranges in closed workbooks.

GoBack button

Clicking this button will GoTo the Name that was last Gone To.

Renaming a Name

You can change the name of a name or table by clicking the Rename Button or hitting F2, to bring up this dialog box.

UnName

This button converts selected Tables and Names back to ranges.

About Button

The About button shows the current version and build number of the component and the developers of Name Manager.

 Dynamic Range Wizard Button

 This button launches the Dynamic Range Wizard. See the Dynamic Range Wizard section of this guide for details.

Find and Replace button

The Find and Replace button launches the find and replace dialog:

Find what:

Enter the text (Find text) you want to be found or replaced in the Refers-To in this box. If you want to use the wildcard characters * and ? then you must also check the Wildcard Find checkbox.

Replace With:

Enter the text (Replace Text) you want to replace the text found in the Refers-To.

Find & Replace Scope

Choose to apply the find and replace operation either to all the names that have been filtered in the names listbox, or just to all the names that are currently selected in the names listbox.

Find Next Button

Finds and selects the next occurrence of the Find Text within the Filtered or Selected names Refers-To.

Close Button

Closes the Find and replace dialog.

Replace Button

Finds the first Refers-To that contains the Find Text within the Find & Replace Scope and replaces it with the Replace text. If the first Refers-To found contains multiple copies of the find text then they are all replaced.
If the Find text cannot be found an error message will be shown.

If Confirm Changes is checked then you will be asked to confirm the replace operation.

Replace All Button

Finds every Refers-To that contains the Find Text within the Find & Replace Scope and replaces them with the Replace Text.

Name Map Button

The Name Map button analyzes all the formulas on the worksheets in the workbook and in other names to produce a Name Map worksheet showing counts of the usage of the Names by worksheet and by other Name.

Names created by INDIRECT formulas are also found.

Name Manager Help Button

The Name Manager Help button provides on-line help about Name Manager

Name Manager Options Listbox

Confirm Changes

Show Excel System Names

Check this box to show Excel’s system names (like Print_Area, Print_Titles, _FilterDatabase). In general it is recommended not to modify these names or to delete them.

Show refersto

Check this box to have the name manager show the refersto properties next to the names listing.

R1C1 Notation

This checkbox may also be used to repair corrupt names in any workbook. Simply click it twice and you will be prompted to rename any corrupt name!

Icons

Check this box to show Icons for the Name manager Action buttons:

Uncheck this box to show Buttons:

Language dropdown

Choose one of the available languages. After pressing OK, this language will be used in all messages and on all user forms.

Name Manager and the VBE (Windows Only)

With version 4.1 we’ve introduced a new nifty tool: a toolbar with a dropdown with range names to pick from in the Visual Basic Editor:

Selecting a name inserts that name at the current insertion point in your code.

Corrupt names

Sometimes a workbook may contain corrupted names, like these:

Names like these cannot be deleted or edited at all using the normal methods.

The name manager will detect this when you try to edit or delete such a name and offer you the opportunity to rename them to a valid name. After changing the invalid names they can be edited and/or deleted.

Problems discovered during the development of this utility

There are some bugs in Excel’s Names collection and Name Object:

Non US List separators

The RefersToLocal property of a name will not accept other list separators than the US default comma. For instance, this code line:

Names(“Test”).RefersToLocal=”=SUM($A$1;$B$1)”

Gives a runtime error, even if the semicolon is set as the list separator in Control panel and XL accepts this formula in its cells. In the name manager a workaround has been used to overcome this problem.

Unusual Characters in Names

When one defines a local name on a Sheet named “Test”:

Name: Test!test                     Refersto: “1”

and renames this sheet to e.g. “Hi! There!” (without the quotes), this name becomes inaccessible for editing and deleting, yielding an invalid name error message.

Other strange characters in sheet names (especially “[“, “]” and char(3) ) may even cause a crash of Excel when trying to access these local sheet names. A check has been included in the name manager to overcome this problem, yielding this message:

Press cancel if you have multiple names selected and wish to abort the process you have chosen.

Duplicate Global Local Names

Accessing a global name whilst the active sheet has an identically named name local to that sheet, will change the properties of the local name and NOT the global name, even if the name is fully qualified with the workbook name. Name Manager bypasses this problem.

Names with refers-to starting with =!

Names with refers-to starting with =! may give incorrect results when calculation is called from VBA. They are calculated as though they always refer to the active worksheet rather than the sheet that they are being used on.

Name manager will show a warning message whenever it finds a name of this type.

Copyright © 2020 Decision Models Ltd