Vba to hide rows based on a cell within another worksheet

[tomwhi]

Hello,

Please can I ask for your help again.

I am looking for VBA to Hide Rows (37:38), Based on value ("No") of a cell (D26) in another worksheet (Control Center). If cell D26 in worksheet "Control Center" is blank or "Yes", I do NOT want hide the row.

Thanks,
Tom

 

[Mr Mahesh]

Hi Tom,

Find the following code to hide rows based on cell D26 in "Control Center" sheet.
If cell value is "No", then it hides rows 37 & 38 on "Output" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Address = "$C$26" Then
        If Target.Value = "No" Then
            Sheets("Output").Rows("37:38").EntireRow.Hidden = True
        Else
            Sheets("Output").Rows("37:38").EntireRow.Hidden = False
        End If
    End If
End Sub

Place the above code under sheet "Control Center" code module.
For more understanding find Attached File at Box.

Thanks,
Mahesh

 

[Momentman]

Mahesh's should work

 

[tomwhi]

Thank you for your response Mahesh. It works a treat!

 

[Mr Mahesh]

Thanks Momentman.

Tom, good to know that it's working well.

Thanks,
Mahesh

 

[tomwhi]

Hey Mahesh, Momentman,

I have an additional question. Is there away to change the code slightly so that it is driven from a formula, rather than a 'change'? i.e. C26, would be a formula, with the result being Yes, No or "".

Thanks
Tom

 

[Momentman]

A change simply means once the cell recalculates, so whether you have a formula in the cell, it shouldn't matter. The code would still work

 

[Mr Mahesh]

Hi Momentman,

There is a Worksheet_Calculate() event procedure in excel. procedure is triggered by any change in a formula.
The below code should work for you.

Private Sub Worksheet_Calculate()
Dim MyRange As Range
Set MyRange = Sheets("Control Center").Range("C26")
If MyRange.Value = "No" Then
Sheets("Output").Rows("37:38").EntireRow.Hidden = True
Else
Sheets("Output").Rows("37:38").EntireRow.Hidden = False
End If
End Sub

Thanks,
Mahesh

 

[Momentman]

Interesting, We learn everyday

 

[tomwhi]

awesome! thanks guys