Knowledge Base

How to Use a Toolbar Button to Open a File Indirectly

Article ID: 142585

Article Last Modified on 10/10/2006

APPLIES TO

This article was previously published under Q142585

SUMMARY

In Microsoft Excel, you can indirectly associate a workbook with a custom button in another workbook by assigning a macro in the associated workbook to the button.

For example, if you have a workbook named "My Workbook" that contains a button, you can indirectly associate a second workbook called "Main Workbook" with that button by assigning a Microsoft Visual Basic for Applications module in "Main Workbook" to the button. When you click the button in "My Workbook," the second workbook, "Main Workbook," is opened automatically.

MORE INFORMATION

Microsoft Excel 5.0 and 7.0

To associate a workbook with a custom button, do the following:
  1. In Microsoft Excel, create a new workbook.
  2. Point to Macro on the Insert menu, and then click Module.
  3. In the module sheet, type the following code: 
           Sub Test()
       End Sub
  4. On the Edit menu, point to Sheet, and then click Hide. This hides the module sheet.
  5. On the View menu, click Toolbars.
  6. In the Toolbars dialog box, click Customize. Select "Custom" in the Categories list box.
  7. To add the button to an existing toolbar, drag one of the custom toolbar buttons over the toolbar and release the mouse button. To create a new toolbar which contains the toolbar button, drag the custom toolbar button over an area in the workbook and release the mouse button.
  8. Select the "Test" macro in the Assign Macro dialog, and click OK. This will assign the macro Test to the custom button.
  9. In the Customize dialog box, click Close.
  10. On the File menu, click Save.
  11. On the File menu, Click Close.
When you click the custom button, your file is opened. (Note that because the sample macro has no commands, it runs without performing any actions).

Microsoft Excel 97 and Microsoft Excel 98

To associate a workbook with a custom button, do the following:
  1. In Microsoft Excel, create a new workbook.
  2. Press ALT+F11 to activate the Visual Basic Editor. (Or, click the Tools menu, point to Macro, and then click Visual Basic Editor.)
  3. Click Module on the Insert menu. In the Code window, type the following code: 
          Sub Test()
      End Sub
  4. Click Close and Return to Microsoft Excel on the File menu to return to the workbook.
  5. On the View menu, point to Toolbars and click Customize.
  6. Click the Command tab in the Toolbars dialog box. Select Macros in the Categories list box.
  7. Drag the Custom Button over a toolbar and release the mouse button.
  8. Right-click the toolbar button and select Assign Macro. Select the "Test" macro in the Assign Macro dialog, and click OK. This will assign the macro Test to the custom button.
  9. In the Customize dialog box, click Close.
  10. On the File menu, click Save.
  11. On the File menu, Click Close.
When you click the custom button, your file is opened; because the sample macro has no commands, it runs without performing any actions.

REFERENCES

Microsoft Excel 97

For more information about customizing toolbars, click the Office Assistant, type customizing toolbars, click Search, and then click to view the appropriate topic.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

Microsoft Excel 7.0 for Windows 95

For more information about customizing toolbars, click Answer Wizard on the Help menu, and type the following on the search line:

How do I create a custom toolbar?

Click Search, and then double-click the "Create a custom toolbar" topic.

Microsoft Excel version 5.0

"Visual Basic User's Guide," version 5.0, Chapter 34, "Customizing Your Workspace"

Additional query words: 5.0 5.00a 5.00c 8.00 XL97 XL7 XL5 XL

Keywords: kbdtacode kbhowto kbprogramming KB142585


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.