Previous Topic Next Topic

Drop down list opens new window

Closed
bzg51n
Posts
1
Registration date
Saturday April 26, 2014
Status
Member
Last seen
April 26, 2014
 - Apr 26, 2014 at 11:25 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 - Apr 28, 2014 at 12:04 PM
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
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
Apr 28, 2014 at 12:04 PM
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
0