XL5: Error Choosing Button After Assigning Macro

• 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>'

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

   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