Use VBA to hide/unhide rows based on cell values

[fhzhkunming]

Hi Folks,

I have a simple situation but I couldn't make it working.
I want to hide Rows 29-31 if D28 = "Yes" (Fig.1), and want to hide only Row 31 if D28 ="No" and D29 = "Number" (Fig. 2). In other words, if D28 = "Yes", Rows 29-31 are empty (Actually contents in these rows were invisible by conditional formatting); if D28 = "No" and D29 = "Number", only Row 31 is empty row (contents invisible). BTW, values of D28 and D29 were automatically copied from another sheet. I want this hide/unhide event to be automatically triggered.

Below is my code. Could anyone please help me out?

Thanks in advance.

Frank

Private Sub Worksheet_Change(ByVal Target As Range)

'MsgBox "Changed: " & Target.Address

    Application.EnableEvents = False 'pervent triggering another change event

If Not Intersect(Target, Range("D28")) Is Nothing Then
    If Range("D28").Value = "Yes" Then
                Range("D29:D31").EntireRow.Hidden = True
            ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
                Range("D31").EntireRow.Hidden = True
            Else
                Range("D29:D31").EntireRow.Hidden = False
    
        End If

End If

Application.EnableEvents = True 're-enable events in the end

End Sub

 

[fhzhkunming]

The cause of that might be a premature interruption of your own code. Run the code below once and try again. Be sure to paste the code of my post #4 in de module of the sheet to be affected.

Sub RunOnce()
    Application.EnableEvents = True
End Sub

Thanks for the suggestion. But it's still not working. I put a line "MsgBox "Changed: " & Target.Address" to show if the VBA is triggered, but it seems not triggered.

 

[GWteB]

In that case the Change event is not suitable in your situation and the Calculation event should be used.

 

[Fluff]

Are the D28 & D29 cells on sheet you want to hide the rows, changed via a data validation drop-down?

 

[fhzhkunming]

Are the D28 & D29 cells on sheet you want to hide the rows, changed via a data validation drop-down?

Yes, D28 & D29 cells are on the sheet I want to hide the rows, but value changed via data validation drop-down in a previous sheet.

 

[Fluff]

If the drop down is on anther sheet, how are the cells being changed?

 

[fhzhkunming]

If the drop down is on anther sheet, how are the cells being changed?

I think I got your point. Once a value is selected from the drop down from another sheet, the value of D28 & 29 has been changed before the sheet I want to hide/unhide the rows is activated. So when the sheet is active, nothing change to trigger the code.
If that is the case, is it possible to trigger the code on the sheet containing the drop down, and make changes (hide/unhide rows) ton the inactive sheet?

Thanks

 

[Fluff]

Yes that's possible, what is the name of the sheet you want to hide the rows on?

 

[fhzhkunming]

It has been proved that the idea is feasible. The problem is solved. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then Exit Sub
    If Range("G26").Value = "Yes" Then
            Sheets("Stage C-Step 11").Rows("29:31").EntireRow.Hidden = True
        ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
            Sheets("Stage C-Step 11").Rows("31").EntireRow.Hidden = True
        Else
            Sheets("Stage C-Step 11").Rows("29:31").EntireRow.Hidden = False

    End If

  
End Sub

Thanks to those have provided help. Appreciate it your brilliant idea or code.

Have a wonderful weekend.

Frank

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[fhzhkunming]

@fhzhkunming, in your code any change of cell D29 isn't captured. How about:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D28:D29")) Is Nothing Then Exit Sub

    If Range("D28").Value = "Yes" Then
        Rows("29:31").Hidden = True

    ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
        Rows("31").Hidden = True

    Else
        Rows("29:31").Hidden = False

    End If

End Sub

​

Thanks for your code that brought me the inspiration to eventually solve the problem.

Best regards,

Frank