9

I'm trying to create a button to take me to my Index sheet. I know how to do it using a macro or a hyperlink, but my users aren't 'smart' users and will be calling me all day if I use macros.

Does anyone know how I could solve this, besides using a hyperlink? I was thinking of a button with a hyperlink, but haven't found a way to do it!

Thank you! :)

CustomX
  • 9,948
  • 30
  • 85
  • 115

5 Answers5

26

In Excel 2007, goto Insert/Shape and pick a shape. Colour it and enter whatever text you want. Then right click on the shape and insert a hyperlink

enter image description here

enter image description here

A few tips with shapes..

If you want to easily position the shape with cells, hold down Alt when you move the shape and it will lock to the cell. If you don't want the shape to move or resize with rows/columns, right click the shape, select size and properties and choose the setting which works best.

11

Alternately, if you are using a Macro Enabled workbook:

Add any control at all from the Developer -> Insert (Probably a button)

When it asks what Macro to assign, choose New. For the code for the generated module enter something like:

Thisworkbook.Sheets("Sheet Name").Activate

However, if you are not using Macros in your work book. Ooo's approach is definitely surperior as hyperlinks will work with no need to trust the document.

Daniel
  • 12,982
  • 3
  • 36
  • 60
2

You have to add Button to excel sheet(say sheet1) from which you can go to another sheet(say sheet2).

Button can be added from Developer tab in excel. If developer tab is not there follow below steps to enable.

GOTO file -> options -> Customize Ribbon -> enable checkbox of developer on right panel -> Done.

To Add button :-

Developer Tab -> Insert -> choose first item button -> choose location of button-> Done.

To give name for button :-

Right click on button -> edit text.

To add code for going to sheet2 :-

Right click on button -> Assign Macro -> New -> (microsoft visual basic will open to code for button) -> paste below code

Worksheets("Sheet2").Visible = True
Worksheets("Sheet2").Activate

Save the file using 'Excel Macro Enable Template(*.xltm)' By which the code is appended with excel sheet.

Rohan Khude
  • 4,455
  • 5
  • 49
  • 47
0

You don't need to create a button. The facility exists by default.

Just right click on the arrow buttons on the bottom left hand corner of the Excel window. These are the arrow buttons which if you left click move left or right one worksheet.

If you right-click on these arrows Excel will pop up a dialogue with a list of worksheets from which you can click to set your chosen sheet active.

Robert
  • 103
  • 7
  • it isn't user friendly to ask your user to do that :P needs to be something they'll see straight away (that's what I needed it for) – CustomX Jan 04 '16 at 07:24
  • OK, a little surprised by that - I could understand if it was a 3 or 4 click process hidden somewhere in the ribbon but to right-click on an always visible icon seems pretty basic to me. – Robert Jan 05 '16 at 08:53
  • the main page is sort of a table of contents, users need to click the icons to get directed to the correct sheet – CustomX Jan 05 '16 at 10:56
-1

Any reason they can't just click on the tab for your sheet when they want it?

Tim
  • 4,051
  • 10
  • 36
  • 60
  • I have about 20 tabs, and my Index tab contains links to the different sheets. But instead of using a hyperlink on those sheets too I wanted to use a button, but if I can't find a solution I'll just have to make do with a hyperlink. =/ – CustomX Aug 22 '12 at 14:59
  • @Tim This doesn't worth to be an answer. Next time you should leave it under comments. Thanks – Ryan Fung Nov 24 '15 at 03:52