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

 

[Rick Rothstein]

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

You can simplify that Calculate code down to this...

Private Sub Worksheet_Calculate()
  Sheets("Output").Rows("37:38").EntireRow.Hidden = Sheets("Control Center").Range("C26").Value = "No"
End Sub

 

[tomwhi]

Hi Mahesh, Momentman,

You have been a great help. I have another question I hope you might be able to help me with. http://www.mrexcel.com/forum/excel-questions/736813-create-pdf-macro-button-please-help.html

Thanks, I really appreciate your input