Previous Topic Next Topic

Drop down list opens new window

[Closed]
Report
Posts
1
Registration date
Saturday April 26, 2014
Status
Member
Last seen
April 26, 2014
 -
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
 -
I would like to create an excel that has a first sheet with drop down selections
<$2m
>$2m
>$5m
>$7m

Depending which is selected a different excel sheet opens. Each dollar value will have a different / corresponding deal sheet. So 1 sheet will be open always and 4 sheets will be hidden. 2 sheets at the most will be open at any one time, 1 before anything is selected
Thanks

1 reply

Answer 1 / 1
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
 486
Hi bzg51n,

In the code below the drop down list (ddl) is located in A1. The ddl contains the values "a" and "b" and those are also the sheet names: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

If Target = vbNullString Then
    Sheets("a").Visible = False
    Sheets("b").Visible = False
End If

If Target = "a" Then
    Sheets("a").Visible = True
    Sheets("b").Visible = False
End If

If Target = "b" Then
    Sheets("b").Visible = True
    Sheets("a").Visible = False
End If

End Sub


Right-click on sheets tab and select view code to paste the code to.

Best regards,
Trowa
Ask a question