bigbee10200
New Member
- Joined
- Jun 25, 2018
- Messages
- 2
Hello everyone,
I'm not well versed on VB or Macros, and that will be Obviously apparent when you see my workbook. I'm trying to write a code that will allow me to hide and unhide sheets based on once cells value. However, my code writing is terrible. I've been racking my head for 2 or 3 weeks. In the "General" tab If cell named "HowManyWells" (Q4) has a selected value, I only want the corresponding tab to be displayed. The code I've been working on is great only if "1" is selected. If I continue the code as written, and select "2", the tab 2 Wells is displayed. However if I go back "1" in "HowManyWells", both the "1 Well" than and the "2 Wells" tab are displayed. The general tab should always be displayed. I just don't know what to do. Thanks for the help in advanced.
Below is the code I was using:
Private Sub Worksheet_Change(ByVal Target As Range)
If [HowManyWells] = "1" Then
Sheets("2 Wells").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If
If [HowManyWells] = "2" Then
Sheets("1 Well").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If
End Sub
I'm not well versed on VB or Macros, and that will be Obviously apparent when you see my workbook. I'm trying to write a code that will allow me to hide and unhide sheets based on once cells value. However, my code writing is terrible. I've been racking my head for 2 or 3 weeks. In the "General" tab If cell named "HowManyWells" (Q4) has a selected value, I only want the corresponding tab to be displayed. The code I've been working on is great only if "1" is selected. If I continue the code as written, and select "2", the tab 2 Wells is displayed. However if I go back "1" in "HowManyWells", both the "1 Well" than and the "2 Wells" tab are displayed. The general tab should always be displayed. I just don't know what to do. Thanks for the help in advanced.
Below is the code I was using:
Private Sub Worksheet_Change(ByVal Target As Range)
If [HowManyWells] = "1" Then
Sheets("2 Wells").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If
If [HowManyWells] = "2" Then
Sheets("1 Well").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If
End Sub