VBA to hide cells based on value of cell in different sheet

[RonnyRaygun]

Hi all,

I was recently given some very useful advice here with regard to hiding cells using VBA. The code looked like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H22").Value = "N" Then
Rows("23:27").EntireRow.Hidden = True
ElseIf Range("H22").Value = "Y" Then
Rows("23:27").EntireRow.Hidden = False
End If
End Sub

I now want to hide rows in "sheet 1" based on the value of a cell in, for example, "sheet 2". I thought this would not be a problem and attempted to do this by linking directly to Sheet 2, and then tried linking a cell in sheet 1 to the relevant cell in sheet 2, and use similar code to that above. However, neither worked. It works fine when I input the value directly but when the value in the cell comes from a link to another tab, it doesn't work.

This is the code I tried that doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("'Sheet 2'!B6").Value = "N" Then
Rows("23:27").EntireRow.Hidden = True
ElseIf Range("'Sheet 2'!B6").Value = "Y" Then
Rows("23:27").EntireRow.Hidden = False
End If
End Sub

Any ideas on what would work in this situation?

 

[mumps]

Place this macro in the code module for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B6")) Is Nothing Then Exit Sub
    If Target = "N" Then
    Sheets("Sheet1").Rows("23:27").EntireRow.Hidden = True
    ElseIf Target = "Y" Then
    Sheets("Sheet1").Rows("23:27").EntireRow.Hidden = False
    End If
End Sub

Enter an "N" or an "H" in cell B6 of Sheet2 to hide/unhide the rows in Sheet1.

 

[RonnyRaygun]

Place this macro in the code module for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B6")) Is Nothing Then Exit Sub
    If Target = "N" Then
    Sheets("Sheet1").Rows("23:27").EntireRow.Hidden = True
    ElseIf Target = "Y" Then
    Sheets("Sheet1").Rows("23:27").EntireRow.Hidden = False
    End If
End Sub

Enter an "N" or an "H" in cell B6 of Sheet2 to hide/unhide the rows in Sheet1.

Thanks, tried that but it still doesn't work.
In the code above, how does Excel know that we are linking to sheet 2? Nothing in the code mentions sheet 2.
I tried "If Intersect(Target, Range("'Sheet2'!B6")) Is Nothing Then Exit Sub" but this doesn't work either.

It's not the linking of the cells that is the problem, as I can link to another cell on the same sheet and have no problems.

The problem is getting Excel to understand that the value in a cell on sheet 2 should hide/unhide rows on sheet 1.

Thanks.

 

[mumps]

The macro is a worksheet change event which is placed in the code module for Sheet2. This means that the macro is run automatically when in your case, any change occurs in cell B6 on Sheet2. Since you have to be on Sheet2 to enter the value in B6, there is no need to have a link to it. It is already active. If you want the hide/unhide actions to take place when another sheet is active, you can't use a worksheet change event. You'll have to use a regular macro and run it manually.