Hidden Row in seperate worksheet.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to hide a row when a cell in a different worksheet says no. Here
is my code:

Private Sub Worksheet_Calculate()
If Worksheets("Hospital").Range("B5").Value = "No" Then
Worksheets("Interview").Range("E9").EntireRow.Hidden = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidden = False
End Sub

It will not work. Can someone assist me?

thank you,

dan
 
Dan,
For sure you are missing two "end If" statements:

Private Sub Worksheet_Calculate()
If Worksheets("Hospital").Range("B5").Value = "No" Then
Worksheets("Interview").Range("E9").EntireRow.Hidden = True
End If
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidden = False
End If
End Sub

Does it work with those inserted?

And if B5 has to be either Yes or No then:

Private Sub Worksheet_Calculate()
If Worksheets("Hospital").Range("B5").Value = "No" Then
Worksheets("Interview").Range("E9").EntireRow.Hidden = True
Else
Worksheets("Interview").Range("E9").EntireRow.Hidden = False
End If
End Sub
 
Hi Dan,

Your code works for me but requires that the sheet holding the code
recalculate.

It might suit your purposes better if you were to use the Worksheet_Change
event. Try putting the following into the Hospital sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Interview").Rows(9).Hidden = _
UCase(Me.Range("B5").Value) = "NO"
End Sub
 
It does work, but only when I am in visual basic and I hit the play button.
It will not do it automactically. I want the row to hide once the cell=no.

dAn
 
Got it. Thank you.


Norman Jones said:
Hi Dan,

Your code works for me but requires that the sheet holding the code
recalculate.

It might suit your purposes better if you were to use the Worksheet_Change
event. Try putting the following into the Hospital sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Interview").Rows(9).Hidden = _
UCase(Me.Range("B5").Value) = "NO"
End Sub
Click to expand...
 
Back
Top