crock
10-18-2022, 07:43 AM
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.
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.