Macro Button

April 8, 2010 at 15:27:07
Specs: Windows Vista
I'm trying to create a button using Macros that will take me to a specific location (cell) on another spreadsheet in the same workbook. The idea is to have a table of contents with a "go to" button, that takes the user directly to each specific section. Is this possible?

See More: Macro Button

Report •

April 8, 2010 at 16:39:43

IMHO, the simplest way to do this is to use a series of cells as the 'buttons' and add hyperlinks to them.

This is for Excel 2003. The process will be a little different for Excel 2007:
Right-click a cell and select 'Insert Hyperlink'
In the dialog box that opens, select the 'Place in this document' in the Links: section on the left.

Fill in the name to display, use the navigation to select the worksheet and enter a cell in the top box.
Add a Tool tip using the ToolTip button.
Click OK.

Change the appearance of the cell - borders, shading, font and font size and re-size the cells as required.

You can merge cells if you don't want to widen the column.


Report •

April 8, 2010 at 17:31:03
Thank you, that was very helpful!

Report •

April 9, 2010 at 03:51:07

If you prefer to do this with buttons:

On the 'Menu' Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to the junp location or whatever.
Right-click the button again and select View Code
In the code window that opens enter this:

Private Sub CommandButton1_Click()
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them.
Change the Sheet name and cell as required.
Repeat with more buttons - they will be automatically numbered e.g., CommandButton2_Click()

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

Click one of the buttons to run the macro and jump to the set location.

PS with the hyperlink method, users can use a 'Back' icon on a menu bar to return to the menu cells. They are on the 'Web' toolbar, but you can move them to one of the other toolbars, so that you don't have to display the Web toolbar itself.

Report •

Related Solutions

Ask Question