How to hide rows if a cell on another sheet has yes or no entered

[Del80y3]

Hi,

some help would be greatly appreicated, I have excel 2013 and am trying to hide 2 rows on a second sheet depending if I enter yes or no on sheet 1.

for example, does a table show on the drawing ? If you enter No in cell H65 on sheet 1 I want it to hide rows 15 & 16 on sheet 2.

many thanks in advance.

 

[Scott T]

Right click on the sheet1 tab and select view code. Past the below code into the VBA editor. You will need to save the file as an macro enabled file type like XLSM. When you change H65 the code will hide or unhide row 15 and 16 on sheet2 based on if it is yes or no. If it is not yes or no then nothing changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
If Target <> ws1.Range("H65") Then Exit Sub
If UCase(ws1.Range("H65")) = "NO" Then
    ws2.Rows("15:16").EntireRow.Hidden = True
ElseIf UCase(ws1.Range("H65")) = "YES" Then
    ws2.Rows("15:16").EntireRow.Hidden = False
End If
 
End Sub

 

[Del80y3]

Thanks Scott T,

How do I save as a Macro enabled file ?

 

[snjpverma]

click on "save as" and in "Save as type" select either Binary or Macro enabled workbook.

 

[Del80y3]

Right so done all of the above, when I enter “No” into cell H65 it comes up with a blue box saying run-time error 9 subscript out of range and asks if I want to Debug.
If I debug it highlights yellow the line Set ws1 = Sheets(“Sheet1”)
is this because I’ve titled the sheets ? Although tried changing sheet 1 for the title and still doesn’t work. Also could it be because I’m on a company laptop which has company security settings installed ?

 

[Scott T]

You will need to change the sheet names to match your sheets. Change Sheet1 to the name of the sheet where you put Yes or No. Change Sheet2 to where you want the rows to be hidden.

 

[snjpverma]

you will have to replace the red part in the code with actual names of the sheets in your workbook.

Set ws1 = Sheets("[B][COLOR=#FF0000]Sheet1[/COLOR][/B]")
Set ws2 = Sheets("[B][COLOR=#FF0000]Sheet2[/COLOR][/B]")

 

[Del80y3]

I’ve tried changing the name of the sheets but still comes up with the run time error.

 

[Scott T]

Is it the same error on the same line?
If it is make sure the sheet name is spelled correctly and there are no extra spaces in the code.