| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My 1st IF-THEN-ELSE Procedure
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
Assuming the code works I would place it into a worksheet module right click on sheet 2 tab and select view code in the dropdown box select worksheet the next box select change enter your code in there, it will look like this with the target as A1 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then 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").Select Rows("1:35").Select Selection.EntireRow.Hidden = True Rows("12:12").Select Selection.EntireRow.Hidden = True Rows("16:16").Select Selection.EntireRow.Hidden = True End If End If End Su -- davesexce ----------------------------------------------------------------------- davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170 View this thread: http://www.excelforum.com/showthread.php?threadid=57341 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
Thanks for the tip Don. I think it will help me consolidate my code.
I'm still green on whether my procedure will work because of the linkage problem with the Target Cell on Sheet1. Amy "Don Guillett" wrote: you may find this idea useful to combine Sub hidenoncontingentrows() Range("a1,a3,a5").EntireRow.Hidden = True End Sub -- Don Guillett SalesAid Software "Jim Cone" wrote in message ... 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 won't work. I'm 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 I've tried to write. Thank for your time, Amy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
Events would be the word to look up not target.
You are using the Worksheet Change event. In the sheet module at the top are two drop down boxes. Set the left box to "Worksheet" and then look at the items displayed in the right box. It shows all the events applicable to that sheet. Click one and see what happens. Remove the code from the Sheet2 module and place the following code in the Sheet1 module... (notice the dots in front of Rows) '--------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then With Worksheets("Sheet2") .Rows("1:35").EntireRow.Hidden = False 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("12:12").EntireRow.Hidden = True .Rows("16:16").EntireRow.Hidden = True End If End With End If End Sub ----------- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "AMY Z." wrote in message 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
Thanks Jim! It works great!
I missed your answer yesterday. Thanks for the Event tip too. I'm going to study it. Amy "Jim Cone" wrote: Events would be the word to look up not target. You are using the Worksheet Change event. In the sheet module at the top are two drop down boxes. Set the left box to "Worksheet" and then look at the items displayed in the right box. It shows all the events applicable to that sheet. Click one and see what happens. Remove the code from the Sheet2 module and place the following code in the Sheet1 module... (notice the dots in front of Rows) '--------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then With Worksheets("Sheet2") .Rows("1:35").EntireRow.Hidden = False 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("12:12").EntireRow.Hidden = True .Rows("16:16").EntireRow.Hidden = True End If End With End If End Sub ----------- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "AMY Z." wrote in message 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My 1st IF-THEN-ELSE Procedure
You guys might laugh at me, but to get around the linkage problem, I set up
an IF Change procedure in Sheet1 A1 that copies and pastes to Sheet2 A1. This eliminates the linkage in the background of Sheet2 A1. Now the IF Change procedure in Sheet2 works. This seems sort of over coding to do it this way, but it's the only way I know right now. I'm just learning. Thanks for everyone taking time to help me. Amy "AMY Z." wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop a Procedure from another procedure | Excel Discussion (Misc queries) | |||
| Calling a procedure in a procedure | Excel Programming | |||
| Calling a procedure in a procedure | Excel Programming | |||
| Calling a procedure in a procedure | Excel Programming | |||
Hybrid Mode
