Hide/Unhide Sheets based on drop down list
Guys,
I have tried a couple codes from Google for this but without success.
Is there any one who can help me with this.
Sheet1--->A1 is drop down list with:
All
FIN
RET
Oth
Now, I would like to create VBA code and when I select "All" to unhide ALL SHEETS then.
If I select FIN, I would like to see "FIN" and to hide RET and Oth.
Else, if I select RET, Would like to hide FIN and Oth and so on.
Ideas?
Thank you in advance.
This will always leave Sheet 1 and whatever other sheet you select from its dropdown in a1 unhidden, and hide the rest when you run the macro. Add it to a button beside your dropdown?
Sub Hide_Sheets() Dim i As Integer Dim SheetCount As Long SheetCount=ThisWorkbook.WorkSheets.Count If Sheets("Sheet1").Cells(1, "A").Value2 = "ALL" Then For i = 2 To SheetCount Sheets(i).Visible = xlSheetVisible Next i Else For i = 2 To SheetCount If Sheets(i).Name = Sheets("Sheet1").Cells(1, "A").Value2 Then Sheets(i).Visible = xlSheetVisible Else Sheets(i).Visible = xlSheetHidden End If Next i End If End Sub
EDIT: My bad, forgot about your "All" request.
Last edited by prjt; 08-31-2016 at 04:27 PM.
Is there any way to get something that I don't need to run macro.
Just on selected "case" from drop down list to hide/unhide specific sheets.
I would like to avoid "button" for macro.
Thanks!
I've found this solution:
But now has another problem. In this Sheet I have already had another VBA in this sheet which is "Worksheet_Change".Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1"), Target) Is Nothing Then Application.ScreenUpdating = False Worksheets("Control Tab").Visible = True Worksheets("Outlook").Visible = True Worksheets("Region FIN").Visible = True Select Case Range("A1").Value Case "Financial" Worksheets("Control Tab").Visible = False Worksheets("Outlook").Visible = False Worksheets("Region FIN").Visible = True Case "Retail" Worksheets("Control Tab").Visible = False Worksheets("Outlook").Visible = True Worksheets("Region FIN").Visible = False 'Case "Watch Specialist" 'Worksheets("Watch Spec GSM Validation").Visible = True End Select Application.ScreenUpdating = True End If End Sub
Is it possible to fix this?
And to be more precise in Sheet1 I have drop down menu in A1 and in A2. For A2 drop down menu as I said I have VBA code for hiding rows and now when I put this new code for hiding Sheets, have conflict between 2 "Private Sub Worksheet_Change".
Last edited by toci; 08-31-2016 at 04:36 PM.
Dim i As Integer Dim SheetCount As Long SheetCount = ThisWorkbook.Worksheets.Count If Sheets("Sheet1").Cells(1, "A").Value2 = "ALL" Then For i = 2 To SheetCount Sheets(i).Visible = xlSheetVisible Next i Else For i = 2 To SheetCount If Sheets(i).Name = Sheets("Sheet1").Cells(1, "A").Value2 Then Sheets(i).Visible = xlSheetVisible Else Sheets(i).Visible = xlSheetHidden End If Next i End If
Dump that into Sheet1's VBA
EDIT: Remove the target statement all together or change the range from "A1" to "A1:A2", and just add my code to the existing "Worksheet Change" code. ....I believe is the solution
Last edited by prjt; 08-31-2016 at 04:42 PM.
Sure, no problem.
Here it is.
As you can see in VBA I have the code for hiding rows which is connected on cell C4.
Now I would like to put your code for hiding sheets based on criteria in C5.
e.g. if I selected in C5 = "All Divisions" then show me all sheets. If it's selected "Financial" hide all except "Region FIN", "Control Tab", "Calculations" and "Actuals".
Or if I select "Retail" ---> hide all except "Region RET", "Control Tab", "Calculations" and "Actuals"
I hope so this is an appropriate example for you.
Thank you very much for help.
I am gonna make an example and put it here because the code above doesn't work for me.
Well you had to change some of the specifics like the sheet name and the dropdown cell since you originally specified A1 and Sheet1. I've done it for you below.
just copy and paste that below your existing code. (Below "Set Changed = Nothing")Dim i As Integer If Sheets("Control Tab").Cells(5, "C").Value2 = "All Divisions" Then For i = 2 To 5 Sheets(i).Visible = xlSheetVisible Next i Else For i = 2 To 5 If Sheets(i).Name = Sheets("Control Tab").Cells(5, "C").Value2 Then Sheets(i).Visible = xlSheetVisible Else Sheets(i).Visible = xlSheetHidden End If Next i End If
just tested it and worked fine to hide and unhide sheets.
Last edited by prjt; 08-31-2016 at 06:06 PM.
Nope, still doesn't work. Only works fine if I select "All Divisions". When I select "Financial" it hides and "Region FIN" but it should be visible.
Also if I select case "Financial" and then "Retail" nothing happens.
I can't figure out on which principle this code works, I mean, how it's know which Sheets to hide?
What if I add some new sheets?
So again... I don't think you really explained yourself very well at all. The code I gave you looks for the value in cell C5 and hides all other sheets WITH THE EXCEPTION of the one with the name matching the value in C5, and the "Control Tab". So either change the names in your dropdown to FIN, RET, etc. OR change the names of your sheets to "Financial", "Retail", etc.Originally Posted by toci
OTHERWISE, if you must have the two be different I suggest using a Vlookup (in either a column you hide or in a locked cell with white text) on your dropdown and having a list of the dropdown options and their corresponding sheets on a separate sheet/hidden columns. If you do this you will have to modify the code I gave you to point to the Vlookup result and NOT c5 where it currently pulling the value from.
Again, the code matches the Value of your dropdown and hides all sheets without the matching name, so if you are using values in your drop down that differ from your sheet names it will not work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks