Hello.
I have been tasked with finding a way to hide all but one sheets in a workbook until a specific cell has a value - then unhide only the sheets which are associated to the given criteria. I am very new to using VBA, but have so far had the limited ability to modify code i've found on the internet just enough to fit my needs - but that is sketchy and sometimes a lengthy process since I barely understand the code.
In my example I have the following in Excel 2010:
Sheet1 is named "Overview" and is always visible. This sheet has a data validation list in cell E10 with 2 options "Green" and "Baker"
Sheet2 is named "GreenSheet" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet3 is named "First" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet4 is named "BakerSheet" and is hidden until cell E10 in "Overview" has "Baker" as its value
Sheet5 is named "Second" and is hidden until cell E10 in "Overview" has "Baker" as its value
If Sheet1 "Overview" cell E10 value is deleted then the associated sheets will be hidden
If Sheet1 "Overview" cell E10 value is changed then the associated sheets will be hidden or made visible based on the value
I searched the net and found a bit of code that will hide all but one sheets on open and I think this is a good start. I have not been able to modify it to also take the cell value into consideration before making this adjustment. I have also not yet gotten to the point of unhiding sheets based on the value of cell E10 in sheet "Overview". I'm hoping you can help me on these two points.
The code I have which hides all sheets on workbook open:
This is being placed into the "ThisWorkbook" object
First: I need to code the workbook to check that sheet "Overview" cell E10 has a Value THEN do nothing ELSE hide all other worksheets (This way we keep the current hidden status of existing workbooks)
Second: I need code that will activate when the value of "E10" changes such that
If Sheet "Overview" Cell "E10" = "Baker" Then Unhide sheets "BakerSheet" and "Second" and Hide All other sheets EXCEPT sheet "Overview"
If Sheet "Overveiw" Cell "E10" = "Green" Then Unhide sheets "GreenSheet" and "First" and Hide All other sheets EXCEPT sheet "Overview"
It should be noted that this workbook will contain at least one worksheet that is always hidden.
Also, in my actual workbook there are more than just the two listed sheets which will be hidden or unhidden based on the above criteria and the total number of associated sheets is not the same between the two conditions (for example Green may have only 1 associated sheet while Baker has 5 associated sheets - the total has not yet been fully determined but will be static once determined), and I think/hope I can modify as needed to make it fit.
I have been tasked with finding a way to hide all but one sheets in a workbook until a specific cell has a value - then unhide only the sheets which are associated to the given criteria. I am very new to using VBA, but have so far had the limited ability to modify code i've found on the internet just enough to fit my needs - but that is sketchy and sometimes a lengthy process since I barely understand the code.
In my example I have the following in Excel 2010:
Sheet1 is named "Overview" and is always visible. This sheet has a data validation list in cell E10 with 2 options "Green" and "Baker"
Sheet2 is named "GreenSheet" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet3 is named "First" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet4 is named "BakerSheet" and is hidden until cell E10 in "Overview" has "Baker" as its value
Sheet5 is named "Second" and is hidden until cell E10 in "Overview" has "Baker" as its value
If Sheet1 "Overview" cell E10 value is deleted then the associated sheets will be hidden
If Sheet1 "Overview" cell E10 value is changed then the associated sheets will be hidden or made visible based on the value
I searched the net and found a bit of code that will hide all but one sheets on open and I think this is a good start. I have not been able to modify it to also take the cell value into consideration before making this adjustment. I have also not yet gotten to the point of unhiding sheets based on the value of cell E10 in sheet "Overview". I'm hoping you can help me on these two points.
The code I have which hides all sheets on workbook open:
This is being placed into the "ThisWorkbook" object
Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name = "Overview" Then
Else
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub
First: I need to code the workbook to check that sheet "Overview" cell E10 has a Value THEN do nothing ELSE hide all other worksheets (This way we keep the current hidden status of existing workbooks)
Second: I need code that will activate when the value of "E10" changes such that
If Sheet "Overview" Cell "E10" = "Baker" Then Unhide sheets "BakerSheet" and "Second" and Hide All other sheets EXCEPT sheet "Overview"
If Sheet "Overveiw" Cell "E10" = "Green" Then Unhide sheets "GreenSheet" and "First" and Hide All other sheets EXCEPT sheet "Overview"
It should be noted that this workbook will contain at least one worksheet that is always hidden.
Also, in my actual workbook there are more than just the two listed sheets which will be hidden or unhidden based on the above criteria and the total number of associated sheets is not the same between the two conditions (for example Green may have only 1 associated sheet while Baker has 5 associated sheets - the total has not yet been fully determined but will be static once determined), and I think/hope I can modify as needed to make it fit.