Worksheet Change with Dependent Drop downs

excelnoviceC · Dec 18th 2019

Pretty new to this coding thing but I like the possibilities it brings. I have been stuck for a few days searching the internet trying to figure out how to perform a Worksheet Change event with 3 different drop downs. I get them all to work independently but I want to combine them so that if I choose 3 different options (in my case, days of workouts, blocks of workouts, and workout types) it pulls up a specific worksheet/program. I bit off a little more than I can chew and I know there is an answer out there of how to combine the ShowSelSheets code to show the one I want but I can't seem to do it by myself. Here is my coding so far and any help is much appreciated!

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("DaysofWorkouts").Address Then
Select Case Target.Value
Case " Days of Workouts"
ShowAllDays
Case ""
'do nothing
Case Else
ShowSelDays
End Select
ElseIf Target.Address = Range("Blocks").Address Then
Select Case Target.Value
Case " Training Blocks"
ShowAllBlocks
Case ""
'do nothing
Case Else
ShowSelDays
ShowSelBlocks
End Select
ElseIf Target.Address = Range("WorkoutTypes").Address Then
Select Case Target.Value
Case " Workout Types"
ShowAllSheets
Case ""
'do nothing
Case Else
ShowSelDays
ShowSelBlocks
ShowSelSheets
'do nothing
End Select
End If
End Sub
Private Sub ShowAllDays()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Private Sub ShowSelDays()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("DaysofWorkouts").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
Private Sub ShowAllBlocks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Private Sub ShowSelBlocks()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("Blocks").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
Private Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub
Private Sub ShowSelSheets()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("WorkoutTypes").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
ws.Activate
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub

Display More

Again, thanks and any help is awesome!

excelnoviceC · Dec 18th 2019

I'm not sure if you are telling me to add code tags into my coding or if my original post should have been formatted correctly. I'm seeing the latter is a common issue with new people and I should have done better. I believe I have added the code tags with this post but if I didn't please let me know. Thanks for the help.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("DaysofWorkouts").Address Then
Select Case Target.Value
Case " Days of Workouts"
ShowAllDays
Case ""
'do nothing
Case Else
ShowSelDays
End Select
ElseIf Target.Address = Range("Blocks").Address Then
Select Case Target.Value
Case " Training Blocks"
ShowAllBlocks
Case ""
'do nothing
Case Else
ShowSelDays
ShowSelBlocks
End Select
ElseIf Target.Address = Range("WorkoutTypes").Address Then
Select Case Target.Value
Case " Workout Types"
ShowAllSheets
Case ""
'do nothing
Case Else
ShowSelDays
ShowSelBlocks
ShowSelSheets
'do nothing
End Select
End If
End Sub
Private Sub ShowAllDays()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Private Sub ShowSelDays()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("DaysofWorkouts").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
Private Sub ShowAllBlocks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Private Sub ShowSelBlocks()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("Blocks").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
Private Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub
Private Sub ShowSelSheets()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Sheet1").Range("WorkoutTypes").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
ws.Activate
Else
If ws.Name <> "Sheet1" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub

Display More

UncleStringer · Dec 19th 2019

Attach a sample workbook

excelnoviceC · Dec 19th 2019

Quote from UncleStringer

Attach a sample workbook

Here is a sample. Like I said, it does what I want it to except getting all 3 to work together. I do feel like I am being redundant on the commands and really the only thing that matters is that once they choose all 3, it grabs that specific worksheet. The whole workbook has a lot more worksheets but I just sent over a few. They are all labeled the same way. Please let me know if you can help.

UncleStringer · Dec 19th 2019

You can search for both strings in the worksheet name

Code

Private Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    Dim a As String, b As String

    a = Range("Blocks").Value
    b = Range("WorkoutTypes").Value

    For Each ws In ActiveWorkbook.Sheets

        If InStr(ws.Name, a) <> 0 And InStr(ws.Name, b) <> 0 Then
            ws.Visible = xlSheetVisible
            ws.Activate
        Else
            If ws.Name <> "Sheet1" Then
                ws.Visible = xlSheetHidden
            End If
        End If
    
    Next ws

End Sub

Display More

excelnoviceC · Dec 19th 2019

Quote from UncleStringer

You can search for both strings in the worksheet name

Code

Private Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    Dim a As String, b As String

    a = Range("Blocks").Value
    b = Range("WorkoutTypes").Value

    For Each ws In ActiveWorkbook.Sheets

        If InStr(ws.Name, a) <> 0 And InStr(ws.Name, b) <> 0 Then
            ws.Visible = xlSheetVisible
            ws.Activate
        Else
            If ws.Name <> "Sheet1" Then
                ws.Visible = xlSheetHidden
            End If
        End If
    
    Next ws

End Sub

Display More

UncleStringer!! You are amazing. I am heading out of the office now but my initial trials have been successful. I did add in the 3rd range of "DaysofWorkouts" and it is still working. I have to put it in the workbook with more worksheets and see if it works. I will mess around with it tomorrow and let you know how it goes. Thanks a billion!

excelnoviceC · Dec 20th 2019

Quote from UncleStringer

You can search for both strings in the worksheet name

Code

Private Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    Dim a As String, b As String

    a = Range("Blocks").Value
    b = Range("WorkoutTypes").Value

    For Each ws In ActiveWorkbook.Sheets

        If InStr(ws.Name, a) <> 0 And InStr(ws.Name, b) <> 0 Then
            ws.Visible = xlSheetVisible
            ws.Activate
        Else
            If ws.Name <> "Sheet1" Then
                ws.Visible = xlSheetHidden
            End If
        End If
    
    Next ws

End Sub

Display More

Thank you again. This worked perfectly.

excelnoviceC · Dec 20th 2019

Resolved. Thank you

Worksheet Change with Dependent Drop downs

Participate now!

Share