Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
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