Buttons for Workbooks

Tony is looking for a way to put buttons on the toolbar that will open regularly used Excel workbooks, without the use of a macro.

Most people use custom toolbar buttons to initiate macros, including macros that open new workbooks. Here, for instance, is a simple macro that could be used to get the desired results:

Public Sub OpenMyWorkbook() 
    Workbooks.Open _
      Filename:="D:\Data\MyWorkbook.xls", _
      UpdateLinks:=False
End Sub 

All you need to do is change the name of the macro and the name of the file you want to open, and you've got a great way to open specific workbooks. If you don't want to use the macro (and many people, such as Tony, prefer not to), then you can follow these steps:

1. Choose Customize from the Tools menu. Excel displays the Customize dialog box.
2. Make sure the Commands tab is selected. (See Figure 1.)



Figure 1. The Commands tab of the Customize dialog box.

3. In the list of Categories, select the Macros category.
4. In the list of Commands, select the Custom Button command.
5. Use the mouse to drag the Custom Button command from the Commands list to its new location on the toolbar. When you release the mouse button, the icon (a smiley face) appears on the toolbar.
6. Right-click the smiley face icon on the toolbar (the icon you just placed). Excel displays a Context menu.
7. In the Name field type a new name for this tool—perhaps the name of the workbook you will open with the button. Just type the name, without pressing Enter.
8. On the Context menu choose Assign Hyperlink | Open. Excel displays the Assign Hyperlink dialog box, which looks like a standard Open dialog box.
9. Use the controls in the dialog box to locate and select the workbook you wanted the button to open.
10. Click OK to accept the workbook you selected.
11. Click on Close to dismiss the Customize dialog box.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2884) applies to Microsoft Excel 97, 2000, 2002, and 2003.