Clicking a cell or button takes you to a certain sheet

jrnyman

Board Regular
Joined
Mar 10, 2002
Messages
105
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
That works great. Is there a way to have a macro automatically insert that into a new sheet that the macro creates?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top