Excel - VBA - Hide cells automatically based on cell value from a formula.

Question

I'm trying to hide and unhide cells from values on a separate sheet using a formula. When I manually enter a value (0 or 1) into the cell the script correctly hides or unhides the rows, but the script cannot read a value of 0 or 1 if the value is coming from a function.

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Activate

If Not Application.Intersect(Range("A8"), Range(Target.Address)) Is Nothing Then

    Select Case Target.Value 

    Case Is = "0": Rows("9:14").EntireRow.Hidden = True 

    Case Is = "1":  Rows("9:14").EntireRow.Hidden = False 

    End Select 

End If

Answer 1

As you have found, Worksheet_Change only reacts to a cell being modified directly by the user. So if the cell contains a formula, Worksheet_Change won't be executed.

One workaround is to check the cells that you do edit directly and that contribute to the result of the formula.

A simple example: A8 contains the formula =SUM(A2:A7) and A2 to A7 are entered/edited directly.

The code could then look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A2:A7"), Target) Is Nothing Then
        Application.EnableEvents = False
        Rows("9:14").EntireRow.Hidden = (Range("A8").Value = 0)
        Application.EnableEvents = True
    End If
End Sub

Another workaround is to use the Worksheet_Calculate event. This is easier, but the disadvantage is that you cannot check which cells have been calculated, and hence the overhead is larger.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Rows("9:14").EntireRow.Hidden = (Range("A8").Value = 0)
    Application.EnableEvents = True
End Sub

Answer 2

As you have found, Worksheet_Change only reacts to a cell being modified directly by the user. So if the cell contains a formula, Worksheet_Change won't be executed.

One workaround is to check the cells that you do edit directly and that contribute to the result of the formula.

A simple example: A8 contains the formula =SUM(A2:A7) and A2 to A7 are entered/edited directly.

The code could then look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A2:A7"), Target) Is Nothing Then
        Application.EnableEvents = False
        Rows("9:14").EntireRow.Hidden = (Range("A8").Value = 0)
        Application.EnableEvents = True
    End If
End Sub

This function did work when A2:A7 were on the same worksheet, but when I tried to pull data from another worksheet into A2:A7 the script stopped functioning. Is there a way to pull data from another worksheet within the macro?

The goal is to pull data from a different worksheet and to have that data automatically tell rows on this sheet to hide or unhide.

Is there an alternative way to achieve this without using a macro?

Note: A15 is not being pulled from another worksheet and is opening A16:A27 correctly using the macro.

Thank you in advance for your help!

Answer 3

Since A2:A7 contain formulas that refer to the sheet Estimate Guide. you would have to create a Worksheet_Change event procedure for that sheet too - adjust the ranges as needed.

So the following code should go into the worksheet module of Estimate Guide:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2:A7"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Worksheets("Ordering Guide")
            .Rows("9:14").EntireRow.Hidden = (.Range("A8").Value = 0)
        End With
    Application.EnableEvents = True
    End If
End Sub