Thanks Dave & Jim for answering my question and taking time to explain this.
It works!
Well sort of.....
Something I didn't think about, Cell A1 in Sheet2 is actually linked to Cell
A1 in Sheet1. Then it doesn't work.
I tried setting the same procedure up in Sheet1 and added a line at the
start of each IF block that says: "Sheets("Sheet2").Select". But it still
hides the rows in Sheet1.
Should I leave the procedure in Sheet2 the way it was, and somehow change
the Target.Address line to: Sheet1 "A1"?
If it is the Target.Address line, could you help with the syntax to set it
up? I can't find an example in any of my books that refers the Target.Address
for another worksheet. Or do I have to start from scratch.
Thank you again for your time. I'm excited that I have at least got the
procedure to work, except for the linkage problem.
"Jim Cone" wrote:
Hello Amy,
You are very close. The code belongs in the Sheet2 module.
It is "event" driven code.
When any cell on the sheet is changed the code will run.
However, the code checks if cell A1 is the changed cell
(the target cell) and if not the code exits...
'---------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Text = "Apples" Then
Rows("10:10").EntireRow.Hidden = True
Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
Rows("15:15").EntireRow.Hidden = True
Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
Rows("30:30").EntireRow.Hidden = True
Rows("35:35").EntireRow.Hidden = True
Else
Rows("1:35").EntireRow.Hidden = False
Rows("12:12").EntireRow.Hidden = True
Rows("16:16").EntireRow.Hidden = True
End If
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"AMY Z."
wrote in message
Hi,
This is what I'm trying to do: It is in Worksheet2. I would like it to run
when cell A1 changes:
If cell A1 says Apples then hide rows 10 & 20.
If A1 says Pears then hide rows 15 & 25.
If A1 says Oranges then hide rows 30 & 35.
If A1 doesn't say any of the above then Unhide all rows (to reset) and hide
rows 12 & 16. (This is Default for all other Fruits).
This is what I wrote but it wont work. Im not getting any code errors, but
it just sits there.
If Worksheets("Sheet2").Range("A1").Text = "Apples" Then
Rows("10:10").Select
Selection.EntireRow.Hidden = True
Rows("20:20").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Pears" Then
Rows("15:15").Select
Selection.EntireRow.Hidden = True
Rows("25:25").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Oranges" Then
Rows("30:30").Select
Selection.EntireRow.Hidden = True
Rows("35:35").Select
Selection.EntireRow.Hidden = True
Else: Worksheets ("Sheet2")
Rows("1:35").Select
Selection.EntireRow.Hidden = False
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("16:16").Select
Selection.EntireRow.Hidden = True
End If
End Sub
Should I put this under the General section of the Sheet2 Object in the VB
editor?
This is the first procedure that Ive tried to write.
Thank for your time,
Amy