Clicking a cell or button takes you to a certain sheet

[jrnyman]

A macro in my workbook produces anywhere up to 20 different sheets, and I would like to find a better way of navigating those sheets than the tabs at the bottom, as you can only see certain ones.

I thought I might set up an additional worksheet with buttons that take you to the specified page, but I've had difficulty. I can produce a list in Column A of all the names of the sheets, but I'm not sure how to create buttons with those names, and furthermore have each button take you directly to that page. I'm thinking it would work by using the Button's name to find the sheet, but i don't know how to access the button's name.

Alternatively, can i make a worksheet where just clicking on a cell takes you to the sheet with that cell's name? Any suggestions?

[Derek]

Hi there

Right click your sheet tab, left click View Code and paste in this event macro code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Sheets(Target.Value).Select
End Sub

Type your sheet names on Sheet1 (or whatever). When you double click the sheet name the macro will select that sheet.

(You can also just right click the sheet navigation buttons at the bottom left of your window and this opens a list of your sheets, click the one you want).

Hope this helps
regards
Derek




[ This Message was edited by: Derek on 2002-04-08 06:15 ]

[jrnyman]

That worked terrific! Now here's the next question: Since I can't copy that list on each sheet as some of them are just charts, is it possible to "freeze" a tab so it is always the first sheet? This way no matter what sheet they go to, they can easily get back to the first one with the list.

[Derek]

Hi again

You could put this event macro into all your other sheets (works for chart sheets too). When you doubleclick anywhere on the sheet it will take you to the switching worksheet (change "switch" to the name of your worksheet)

This is the code for Charts

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Sheets("switch").Select
End Sub

This is the code for normal sheets:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("switch").Select
End Sub


Hope this works for you
regards
Derek


[ This Message was edited by: Derek on 2002-04-08 07:38 ]

[jrnyman]

That works great. Is there a way to have a macro automatically insert that into a new sheet that the macro creates?

[Derek]

Ah, yes I think this is possible, only I'd only be guessing at how its done. Perhaps one of the other VB experts can help me out here.
Good luck
Derek