Drop-down menu and additional sheets
 

I have created a drop-down menu in which specific categories are linked to
additional sheets in the workbook. When a category in the drop-down menu is
selected I want the specific sheets related to that category to appear - how
do I do this? I can find ways to have additional drop-downs pop up on the
same sheet but not separate worksheets. Thanks
--
DBW

Drop-down menu and additional sheets
 

You use the term "specific sheets to appear"

Are multiple sheets referenced by the chosen category?

Only one sheet can be visible at once unless you have multiple windows open
with a sheet in each window.

Or do you mean each category is linked to one sheet and you would like that
sheet to be selected/activated?


Gord Dibben MS Excel MVP


On Thu, 5 Mar 2009 09:36:02 -0800, Willco3
wrote:

I have created a drop-down menu in which specific categories are linked to
additional sheets in the workbook. When a category in the drop-down menu is
selected I want the specific sheets related to that category to appear - how
do I do this? I can find ways to have additional drop-downs pop up on the
same sheet but not separate worksheets. Thanks

Drop-down menu and additional sheets
 

--
DBW


"Gord Dibben" wrote:

You use the term "specific sheets to appear"

Are multiple sheets referenced by the chosen category?

Only one sheet can be visible at once unless you have multiple windows open
with a sheet in each window.

Or do you mean each category is linked to one sheet and you would like that
sheet to be selected/activated?


Gord Dibben MS Excel MVP


On Thu, 5 Mar 2009 09:36:02 -0800, Willco3
wrote:

I have created a drop-down menu in which specific categories are linked to
additional sheets in the workbook. When a category in the drop-down menu is
selected I want the specific sheets related to that category to appear - how
do I do this? I can find ways to have additional drop-downs pop up on the
same sheet but not separate worksheets. Thanks


Each category is linked to one sheet and you would like that
sheet to be activated for completion once a category is selected from the
drop-down menu.

Drop-down menu and additional sheets
 

Assumptions for this example..................

You have 10 sheets. You have 10 categories.

Place category items in D1:D10

Place sheet names in E1:E10

DV dropdown in A1 using D1:D10 as source range.

Right-click on the sheet tab and copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isht As Worksheet
Dim R As Range

Set R = Range("A1")
If Intersect(Target, R) Is Nothing Then Exit Sub

If Not IsError(Application.Match(Target.Value, _
Me.Range("D1:D10"), 0)) Then

isht = Application.VLookup(Target.Value, _
Me.Range("D1:E10"), 2, False)

Sheets(isht).Select
End If
End Sub

Pick a category from A1 to select a sheet.


Gord


On Fri, 6 Mar 2009 06:04:01 -0800, Willco3
wrote:

Each category is linked to one sheet and you would like that
sheet to be activated for completion once a category is selected from the
drop-down menu.

Drop-down menu and additional sheets
 

--
DBW


"Gord Dibben" wrote:

Assumptions for this example..................

You have 10 sheets. You have 10 categories.

Place category items in D1:D10

Place sheet names in E1:E10

DV dropdown in A1 using D1:D10 as source range.

Right-click on the sheet tab and copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isht As Worksheet
Dim R As Range

Set R = Range("A1")
If Intersect(Target, R) Is Nothing Then Exit Sub

If Not IsError(Application.Match(Target.Value, _
Me.Range("D1:D10"), 0)) Then

isht = Application.VLookup(Target.Value, _
Me.Range("D1:E10"), 2, False)

Sheets(isht).Select
End If
End Sub

Pick a category from A1 to select a sheet.


Gord


On Fri, 6 Mar 2009 06:04:01 -0800, Willco3
wrote:

Each category is linked to one sheet and you would like that
sheet to be activated for completion once a category is selected from the
drop-down menu.

Thank you - I'll try it.