Hidden Row in seperate worksheet.
 

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.Hidd en = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End Sub

It will not work. Can someone assist me?

thank you,

dan

Hidden Row in seperate worksheet.
 

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.Hidd en = True
End If
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = 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.Hidd en = True
Else
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End If
End Sub


--
Ken Hudson


"Daniel R. Young" wrote:

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.Hidd en = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End Sub

It will not work. Can someone assist me?

thank you,

dan

Hidden Row in seperate worksheet.
 

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


---
Regards,
Norman



"Daniel R. Young" wrote in message
...
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.Hidd en = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End Sub

It will not work. Can someone assist me?

thank you,

dan

Hidden Row in seperate worksheet.
 

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

"Ken Hudson" wrote:

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.Hidd en = True
End If
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = 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.Hidd en = True
Else
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End If
End Sub


--
Ken Hudson


"Daniel R. Young" wrote:

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.Hidd en = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End Sub

It will not work. Can someone assist me?

thank you,

dan

Hidden Row in seperate worksheet.
 

Got it. Thank you.


"Norman Jones" wrote:

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


---
Regards,
Norman



"Daniel R. Young" wrote in message
...
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.Hidd en = True
If Worksheets("Hospital").Range("B5").Value = "Yes" Then
Worksheets("Interview").Range("E9").EntireRow.Hidd en = False
End Sub

It will not work. Can someone assist me?

thank you,

dan