Results 1 to 4 of 4

Thread: Hide Rows and Run Automatically with Change Please

  1. #1
    Neophyte MeganSalt's Avatar
    Join Date
    Jul 2020
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Hide Rows and Run Automatically with Change Please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    257
    Articles
    0
    Excel Version
    2007
    .
    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.

  3. #3
    Neophyte MeganSalt's Avatar
    Join Date
    Jul 2020
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Logit View Post
    .


    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.

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    796
    Articles
    0
    Excel Version
    Excel 2010
    @ 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 ?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •