Drop-down menu and additional sheets

[Willco3]

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

 

[Gord Dibben]

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

 

[Willco3]

--
DBW

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

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.

 

[Gord Dibben]

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

You have 10 sheets. You have 10 categories.

Place category items in D110

Place sheet names in E1:E10

DV dropdown in A1 using D110 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("D110"), 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

 

[Willco3]

--
DBW

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

You have 10 sheets. You have 10 categories.

Place category items in D110

Place sheet names in E1:E10

DV dropdown in A1 using D110 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("D110"), 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




Thank you - I'll try it.