Drop down list to select worksheet

[Mark McDonough]

What I need to do is have a drop down list with various suppliers making up
the names in the drop down list. The workbook has several worksheets - one
each for each supplier.

What I would like to know is how do I make a selection of a particular
supplier from the drop down box which then takes me to the worksheet
represented by that supplier?

Any help most appreciated.

Cheers

Mark

 

[Gord Dibben]

Mark

Example event code.

Option Compare Text
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 "Supplier1"
Sheets("Sheet1").Select
Case "Supplier2"
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.


Gord Dibben MS Excel MVP

What I need to do is have a drop down list with various suppliers making up
the names in the drop down list. The workbook has several worksheets - one
each for each supplier.

What I would like to know is how do I make a selection of a particular
supplier from the drop down box which then takes me to the worksheet
represented by that supplier?

Any help most appreciated.

Cheers

Mark

Gord Dibben MS Excel MVP

 

[Earl Kiosterud]

Mark,

Gord has given you some macro code that looks as though it'll do great. He
didn't mention that you'll also need to make a list of the sheet names, each
in a cell, for it to work. As you add sheets, you'll need to keep the list
current. You may also want a button on each sheet that will return you to
the list, which could be a cell you set up with "Insert - Hyperlink." Use
"Place in this document."

Another solution is to right-click any of the sheet navigation buttons at
the bottom left. It produces a list of sheets, though if there are many,
you have to go through yet another click. Users need to know about this,
but they don't with Gord's solution.

 

[Gord Dibben]

Why would Mark need a list of sheet names, each in a cell?

The code I provided uses the drop-down selection as a case select to go to the
hard-coded worksheet for that supplier.

Any addition of sheets would require amendments to the code.

Yes, there are other ways to get to sheets but they do not address the question
of how to use a drop-down to go to a specific sheet.


Gord

 

[Mark McDonough]

Thanks guys. I'll give this a go at work tomorrow.

 

[Mark McDonough]

Thanks for your help here. It works well. Most impressed.

Extending on that idea, in a multi page workbook, what I'd like to do is to
start from a drop down list and select a supplier (one worksheet for each)
and bring into the front summary sheet the data that resides in cell C75 in
each of the identical worksheets.

eg. drop down list has coy ABC P/L. Select ABC Pty Ltd and (code??) drop the
number that resides in cell C75 into the front sheet called "Summary" also
in C75.

if I select another supplier in the drop down list then that supplier's
worksheet cell C75 is brought to the front sheet.