Creating Buttons To Run Code

Refer to the part one file for the beginning of the lesson.

Now we put the finishing touches on the table of contents project. See the past two lessons to see how to record the initial code, then trim it and copy it to create the macros for the other sheets.

We are done recording and building the macro. Now let's complete the index:

Return to Excel. Go to the Index worksheet.
Put in the name of each sheet in column B.
Make the 3 rows wider where you have the worksheet names in the index.
Right click on the Toolbar and open up the Forms toolbar.
The Forms toolbar should appear in the middle of the worksheet or in the toolbar area at the top.
Click on the button icon on the Forms toolbar (this icon looks like a rectangle)
Now you will see a crosshair pointer for your mouse.
Take this to column A to the left of where you entered First in column B for your first worksheet to be selected.
Draw a rectangle in the cell in Column A.
A dialog box will popup which says Assign Macro. Click on SelectFirst.
Click OK
Now go to the button, and rename it for the page number, which will be 2, since the Index is the first page. To rename it, right click on the button, then select Edit Text.
You have just created the first button. Go ahead and test it out. Click on it.

To create buttons for the other sheets:

Copy the first button.
Paste it to where you want it for the next button.
Right click on the button, select Edit and rename it.
Right click on the button, select Assign Macro and assign the macro for the other sheet.

See this image for what the finished product could look like.

Remember to test each of the buttons.

To complete the loop, you may also want to create a button on each of the non index sheets to go back to the index. To review, you would:

Copy the procedure for selecting one of the sheets, such as SelectFirst
Paste and modify to create a procedure called SelectIndex
Put a button on the First sheet called Index and link it to SelectIndex.
Copy this button to the other sheets

Now you have created a workbook that makes it easy for your user to navigate around the sheets.

Return To Excel Tips Index