| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
| How do I have seperate headers for seperate pages in 1 worksheet? | Excel Discussion (Misc queries) | |||
| Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) | |||
| How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
| Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming | |||
Linear Mode
