Worksheet Change Referencing Cell in Another Sheet

[crock]

Hello,

I have two sheets - one with user inputs, and another that uses if statements to populate info based on the user inputs. One formula on Sheet1 is IF(Sheet2!A1="A","Yes","No"). I'm trying to use VBA to hide row 2 on Sheet1 sheet if the formula result is "No". I can make the code work when the IF formula references a cell on the same sheet, but not when it references a cell on a separate sheet.

Here is the code that is not working. It only works if, after changing the value in Sheet2, I then go back to Sheet1, double click on any cell and hit enter. Changing the reference cell on Sheet2 is not enough to trigger the code, even though it correctly changes the result in Sheet1!A1. If I change Sheet1!A1 formula to reference an input cell on Sheet1 instead of Sheet2, though, changing the reference cell alone does trigger the code.



Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = "No" Then
Rows("2:2").EntireRow.Hidden = True
ElseIf Range("A1").Value = "Yes" Then
Rows("2:2").EntireRow.Hidden = False


End If
End Sub


Here is something else I tried that isn't working. I can also make this work by double clicking any cell on Sheet1 and hitting enter, but just changing the reference cell on the Sheet2 doesn't trigger the code. This one is really not ideal, because Sheet2!A1 can have more allowable values than "A" and "B", and I basically need to hide the rows only for "B" and show the rows for all other values.



Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet2").Range("A1").Value = "B" Then
Worksheets("Sheet1").Rows("2:2").EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Value = "A" Then
Worksheets("Sheet1").Rows("2:2").EntireRow.Hidden = False


End If
End Sub


What do I need to do for the code to recognize the cell change? I can't put the inputs on the same sheet as the results, because I'll eventually have one input sheet feeding into multiple output sheets and need rows to hide for proper formatting.

[crock]

I ended up figuring this out. I put a button on the input sheet (Sheet 1) to activate Sheet 2, select a blank cell and hit enter.



Sub Go()
If Range("B7").Value = 1 Then
ThisWorkbook.Sheets("Sheet2").Activate
Range("a1").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
Else
ThisWorkbook.Sheets("Sheet3").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
End If
End Sub

[Aussiebear]

Maybe change that to



Sub Go()
If Range ("B7") . Value = 1 Then
With Worksheets("Sheet2")
.Range("A1").FormulaR1C1.Value = ""
.Offset(A1,1, ).Activate
End With
Else
With Worksheets("Sheet3"
.Range("A1").FormulaR1C1.Value = ""
.Offset(A1,1, ).Activate
End With
End If
End Sub



Mind you this only removes the value of the first cell in row 1 of either sheet

[snb]

.Offset(A1,1, ).Activate

What is this supposed to mean ?

[p45cal]

You can do it something like this:
Private Sub Worksheet_Change(ByVal Target As Range) 'this is in the Sheet2 code-module.
If Not Intersect(Target, Range("A1")) Is Nothing Then 'will be true if cell A1 has been changed.
'examine and act on Sheet1:
Sheets("Sheet1").Rows("2:2").EntireRow.Hidden = Sheets("Sheet1").Range("D4").Value = "Yes"
End If
End Sub

I might have got the Yes/No Hidden/Not hidden the wrong way.
No code needed in Sheet1's code module.
See attached.