Resolved. Thank you
Posts by excelnoviceC
-
-
You can search for both strings in the worksheet name
Code
Display MorePrivate 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 SubThank you again. This worked perfectly.
-
You can search for both strings in the worksheet name
Code
Display MorePrivate 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 SubUncleStringer!! 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!
-
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.
-
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
Display MorePrivate 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 -
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
Display MorePrivate 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 SubAgain, thanks and any help is awesome!