How do you create a menu for just that specific worksheet?

G

gregesau

I have a model that has several sheets. I would like to have a drop-down menu
on the menu toolbar that would allow me to move select the sheet I want to go
to by using a dropdown menu.

Any help would be appreciated. Thanks.
 
G

Gord Dibben

In case you didn't know, if you right-click on the navigation arrows at bottom
left you will get a list of sheets to choose from.

You could also use a Data Validation drop-down list and some event code to take
you to the chosen sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "1"
Sheets("Sheet1").Select
Case "2"
Sheets("Sheet2").Select
End Select
endit:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code". Copy and paste into that module.

Adjust range and parameters to suit.

If that won't do the job here's a post by Dave Peterson

Start Dave post.................................

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

End Dave post...........................


Gord Dibben MS Excel MVP
 
Top