MeganSalt
New member
- Joined
- Jul 11, 2020
- Messages
- 2
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016
First time posting, so I apologize for any trouble in advance!
I have a workbook with 5 worksheets. Data on the first sheet determines the hiding (or not), of rows on the other sheets.
I created the below. (Very long, but it seemed to work, so I ran with it.)
I need the below to always run if there is ever a change on Sheet1. I've gone to several different sites/videos today, and they keep wanting me to do Worksheet/Change, and adding in a message box. I'm at my wits end, and I feel there is (hopefully), a simple solution that I'm too blind to see.
Private Sub Sheet1_Change(ByVal Target As Range)
End Sub
Private Sub Worksheet_Activate()
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet2.Rows("9").EntireRow.Hidden = True
Else
Sheet2.Rows("9").EntireRow.Hidden = False
End If
If Target = Range("B24") Then
If Sheet1.Range("B24").Value = "No" Then
Sheet2.Rows("10").EntireRow.Hidden = True
Else
Sheet2.Rows("10").EntireRow.Hidden = False
End If
If Target = Range("B33") Then
If Sheet1.Range("B33").Value = "No" Then
Sheet2.Rows("11").EntireRow.Hidden = True
Else
Sheet2.Rows("11").EntireRow.Hidden = False
End If
If Target = Range("E27") Then
If Sheet1.Range("E27").Value = "No" Then
Sheet2.Rows("12").EntireRow.Hidden = True
Else
Sheet2.Rows("12").EntireRow.Hidden = False
End If
If Target = Range("B29") Then
If Sheet1.Range("B29").Value = "No" Then
Sheet2.Rows("13").EntireRow.Hidden = True
Else
Sheet2.Rows("13").EntireRow.Hidden = False
End If
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet5.Rows("16:21").EntireRow.Hidden = True
Else
Sheet5.Rows("16:21").EntireRow.Hidden = False
End If
If Target = Range("E26") Then
If Sheet1.Range("E26").Value = "No" Then
Sheet5.Rows("22:26").EntireRow.Hidden = True
Else
Sheet5.Rows("22:26").EntireRow.Hidden = False
End If
If Target = Range("B41") Then
If Sheet1.Range("B41").Value = "No" Then
Sheet5.Rows("43:45").EntireRow.Hidden = True
Else
Sheet5.Rows("43:45").EntireRow.Hidden = False
End If
If Target = Range("B40") Then
If Sheet1.Range("B40").Value = "No" Then
Sheet5.Rows("46:48").EntireRow.Hidden = True
Else
Sheet5.Rows("46:48").EntireRow.Hidden = False
End If
If Target = Range("B21") Then
If Sheet1.Range("B21").Value = "No" Then
Sheet5.Rows("29:30").EntireRow.Hidden = True
Else
Sheet5.Rows("29:30").EntireRow.Hidden = False
End If
If Target = Range("B39") Then
If Sheet1.Range("B39").Value = "No" Then
Sheet5.Rows("31").EntireRow.Hidden = True
Else
Sheet5.Rows("31").EntireRow.Hidden = False
End If
If Target = Range("B36") Then
If Sheet1.Range("B36").Value = "No" Then
Sheet5.Rows("32:36").EntireRow.Hidden = True
Else
Sheet5.Rows("32:36").EntireRow.Hidden = False
End If
If Target = Range("B23") Then
If Sheet1.Range("B23").Value = "No" Then
Sheet5.Rows("37:39").EntireRow.Hidden = True
Else
Sheet5.Rows("37:39").EntireRow.Hidden = False
End If
If Target = Range("B37") Then
If Sheet1.Range("B37").Value = "No" Then
Sheet5.Rows("40:42").EntireRow.Hidden = True
Else
Sheet5.Rows("40:42").EntireRow.Hidden = False
End If
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet4.Rows("7").EntireRow.Hidden = True
Else
Sheet4.Rows("7").EntireRow.Hidden = False
End If
If Target = Range("E26") Then
If Sheet1.Range("E26").Value = "No" Then
Sheet4.Rows("8").EntireRow.Hidden = True
Else
Sheet4.Rows("8").EntireRow.Hidden = False
End If
If Target = Range("B24") Then
If Sheet1.Range("B24").Value = "No" Then
Sheet4.Rows("4").EntireRow.Hidden = True
Else
Sheet4.Rows("4").EntireRow.Hidden = False
End If
If Target = Range("B33") Then
If Sheet1.Range("B33").Value = "No" Then
Sheet4.Rows("5").EntireRow.Hidden = True
Else
Sheet4.Rows("5").EntireRow.Hidden = False
End If
If Target = Range("B35") Then
If Sheet1.Range("B35").Value = "No" Then
Sheet4.Rows("6").EntireRow.Hidden = True
Else
Sheet4.Rows("6").EntireRow.Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Any assistance would be appreciated. This was thrown at me unexpectedly, and I have been doing my best, but I honestly don't have a ton of VBA experience, and have not been able to find another resource willing to help.
I have a workbook with 5 worksheets. Data on the first sheet determines the hiding (or not), of rows on the other sheets.
I created the below. (Very long, but it seemed to work, so I ran with it.)
I need the below to always run if there is ever a change on Sheet1. I've gone to several different sites/videos today, and they keep wanting me to do Worksheet/Change, and adding in a message box. I'm at my wits end, and I feel there is (hopefully), a simple solution that I'm too blind to see.
Private Sub Sheet1_Change(ByVal Target As Range)
End Sub
Private Sub Worksheet_Activate()
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet2.Rows("9").EntireRow.Hidden = True
Else
Sheet2.Rows("9").EntireRow.Hidden = False
End If
If Target = Range("B24") Then
If Sheet1.Range("B24").Value = "No" Then
Sheet2.Rows("10").EntireRow.Hidden = True
Else
Sheet2.Rows("10").EntireRow.Hidden = False
End If
If Target = Range("B33") Then
If Sheet1.Range("B33").Value = "No" Then
Sheet2.Rows("11").EntireRow.Hidden = True
Else
Sheet2.Rows("11").EntireRow.Hidden = False
End If
If Target = Range("E27") Then
If Sheet1.Range("E27").Value = "No" Then
Sheet2.Rows("12").EntireRow.Hidden = True
Else
Sheet2.Rows("12").EntireRow.Hidden = False
End If
If Target = Range("B29") Then
If Sheet1.Range("B29").Value = "No" Then
Sheet2.Rows("13").EntireRow.Hidden = True
Else
Sheet2.Rows("13").EntireRow.Hidden = False
End If
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet5.Rows("16:21").EntireRow.Hidden = True
Else
Sheet5.Rows("16:21").EntireRow.Hidden = False
End If
If Target = Range("E26") Then
If Sheet1.Range("E26").Value = "No" Then
Sheet5.Rows("22:26").EntireRow.Hidden = True
Else
Sheet5.Rows("22:26").EntireRow.Hidden = False
End If
If Target = Range("B41") Then
If Sheet1.Range("B41").Value = "No" Then
Sheet5.Rows("43:45").EntireRow.Hidden = True
Else
Sheet5.Rows("43:45").EntireRow.Hidden = False
End If
If Target = Range("B40") Then
If Sheet1.Range("B40").Value = "No" Then
Sheet5.Rows("46:48").EntireRow.Hidden = True
Else
Sheet5.Rows("46:48").EntireRow.Hidden = False
End If
If Target = Range("B21") Then
If Sheet1.Range("B21").Value = "No" Then
Sheet5.Rows("29:30").EntireRow.Hidden = True
Else
Sheet5.Rows("29:30").EntireRow.Hidden = False
End If
If Target = Range("B39") Then
If Sheet1.Range("B39").Value = "No" Then
Sheet5.Rows("31").EntireRow.Hidden = True
Else
Sheet5.Rows("31").EntireRow.Hidden = False
End If
If Target = Range("B36") Then
If Sheet1.Range("B36").Value = "No" Then
Sheet5.Rows("32:36").EntireRow.Hidden = True
Else
Sheet5.Rows("32:36").EntireRow.Hidden = False
End If
If Target = Range("B23") Then
If Sheet1.Range("B23").Value = "No" Then
Sheet5.Rows("37:39").EntireRow.Hidden = True
Else
Sheet5.Rows("37:39").EntireRow.Hidden = False
End If
If Target = Range("B37") Then
If Sheet1.Range("B37").Value = "No" Then
Sheet5.Rows("40:42").EntireRow.Hidden = True
Else
Sheet5.Rows("40:42").EntireRow.Hidden = False
End If
If Target = Range("E21") Then
If Sheet1.Range("E21").Value = "No" Then
Sheet4.Rows("7").EntireRow.Hidden = True
Else
Sheet4.Rows("7").EntireRow.Hidden = False
End If
If Target = Range("E26") Then
If Sheet1.Range("E26").Value = "No" Then
Sheet4.Rows("8").EntireRow.Hidden = True
Else
Sheet4.Rows("8").EntireRow.Hidden = False
End If
If Target = Range("B24") Then
If Sheet1.Range("B24").Value = "No" Then
Sheet4.Rows("4").EntireRow.Hidden = True
Else
Sheet4.Rows("4").EntireRow.Hidden = False
End If
If Target = Range("B33") Then
If Sheet1.Range("B33").Value = "No" Then
Sheet4.Rows("5").EntireRow.Hidden = True
Else
Sheet4.Rows("5").EntireRow.Hidden = False
End If
If Target = Range("B35") Then
If Sheet1.Range("B35").Value = "No" Then
Sheet4.Rows("6").EntireRow.Hidden = True
Else
Sheet4.Rows("6").EntireRow.Hidden = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Any assistance would be appreciated. This was thrown at me unexpectedly, and I have been doing my best, but I honestly don't have a ton of VBA experience, and have not been able to find another resource willing to help.