Hide Rows and Run Automatically with Change Please

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've gone to several different sites/videos today, and they keep wanting me to do Worksheet/Change

And that is because you said :

I need the below to always run if there is ever a change on Sheet1

Using the Worksheet/Change is how it is done with VBA. You can skip the MessageBox ... that doesn't have anything to do with your goal.
 
.


And that is because you said :



Using the Worksheet/Change is how it is done with VBA. You can skip the MessageBox ... that doesn't have anything to do with your goal.

Thank you - but when I entered that in, it is not running the macro when there is a change. I tried it with and without the message box.
 
@ MeganSalt

A sample file goes a long way towards clarifying what/where/how you're trying things.

With what you've provided, is it correct to assume
. what you refer to as Sheet1 is where the changes take place ?
. you know you're referring to the sheets by codename and not the tab names ?
. the code is in the Sheet1 module not a standard module ?
. none of the cells to be monitored for change contain formulas ?
. you know that in the code cell values will be case sensitive ?
 
Back
Top