XL5: Error Choosing Button After Assigning Macro

ID: Q119437

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you use a Visual Basic procedure to assign a macro to a custom toolbar button, you receive the following error message when you choose the button

Cannot find macro ''C:\EXCEL\<BOOK.XLS>'!<Assigned_Macro>'

where <Assigned_Macro> is the name of the macro you assigned to the button, and <BOOK.XLS> is the name of an open workbook that contains a macro, but does not contain Assigned_Macro.

CAUSE

This behavior occurs when you use a procedure contained in one workbook to assign a macro contained in the active workbook to a toolbar button. In this case, the name of the workbook from which you run the procedure to assign the macro is incorrectly added to the macro name that is assigned to the button.

For example, if you use a procedure in BOOK.XLS to assign the macro Assigned_Macro contained in MACRO.XLS to a toolbar button, and MACRO.XLS is the active workbook, the following macro name is assigned to the button:


   BOOK.XLS!Assigned_Macro

This behavior occurs only when you use the xlDialogAssignToTool named constant to display the Assign Macro dialog box as in the following example:


   Application.Dialogs(xlDialogAssignToTool).Show arg1:="Toolbar1", arg2:=1

WORKAROUNDS

To avoid this behavior, do either of the following:

Assign the macro to the toolbar button using the Assign Macro dialog box by using the following steps:
1. From the View menu, choose Toolbars. Choose the Customize button.
2. Choose the custom button (on the toolbar) to which you want to assign the macro.
3. From the Tools menu, choose Assign Macro. From the Macro Name/Reference list, select the macro you want to assign, and choose OK.
4. Close the Customize dialog box.
-or-

Use the OnAction method of the ToolbarButtons object to assign the macro as in the following example:

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.
```
      Sub Assign_To_Button()
         ' The following line assigns the macro Assigned_Macro
         ' to the first button on the toolbar named Toolbar1
         Toolbars("Toolbar1").ToolbarButtons(1).OnAction = "Assigned_Macro" 
```
End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in a later version of Microsoft Excel.

REFERENCES

For more information about Assigning A Macro To A Custom Button On A Toolbar, choose the Search button in Help and type:


   assigning macros

Additional query words: 1.00 err msg


Keywords          : 
Version           : 5.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: July 14, 1999