Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

Excel Worksheet Selection


  • Please log in to reply

#1
STARZ126

STARZ126

    New Member

  • Member
  • Pip
  • 1 posts
I am hoping there is a way to create a drop down menu in Excel 2007 to bring the user to a specific worksheet.

I have several worksheets and to make it easier on the users I want to make a main "instruction" worksheet and from this worksheet you could see a drop down menu and pick which sheet you need to update/view.

I understand there are tabs at the bottom, but I am trying to make this as easy (and effective) as possible for the potential users of my spreadsheet. There are >50 worksheets so you wouldn't be able to see them all at once (which is why the drop down would be perfect!)
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Create your drop-down in A1 on Sheet1. Right-click on the Sheet1 tab and select View Code. Enter the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Dim ws As Worksheet
For Each ws In Worksheets
Sheets(ws.Name).Visible = True
Next ws

Select Case [A1].Value
	Case "Worksheet name 01"
	  Sheets("Worksheet name 01").Select
	Case "Worksheet name 02"
	  Sheets("Worksheet name 02").Select
	Case "Worksheet name 03"
	  Sheets("Worksheet name 03").Select
	Case "Worksheet name 04"
	  Sheets("Worksheet name 04").Select
	Case "Worksheet name 05"
	  Sheets("Worksheet name 05").Select
	Case "Worksheet name 06"
	  Sheets("Worksheet name 06").Select
	  
  End Select
End Sub

Obviously you can change the index cell from A1, put your own worksheet names in, and add more worksheets as necessary.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP