| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than one Target.Addresses
Hi,
Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Hi Amy,
The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
One possible way:
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 If Range("B1").Value = "Red Delicious" _ Then .Rows("10:10").EntireRow.Hidden = True .Rows("20:20").EntireRow.Hidden = True Else End If 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 Regards, Paul "AMY Z." wrote in message ... Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Look for Range in VBA's help (instead of looking for Target).
Maybe you could do something with this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("a1:b1")) Is Nothing Then Exit Sub Select Case LCase(Target.Address(0, 0)) Case Is = LCase("A1") Select Case LCase(Target.Value) Case Is = LCase("apples") 'hide apples stuff Case Is = LCase("Pears") 'hide pears End Select Case Is = LCase("B1") Select Case LCase(Target.Value) Case Is = LCase("apples") 'hide apples stuff Case Is = LCase("Pears") 'hide pears End Select End Select End Sub AMY Z. wrote: Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Thanks Jim, Paul, and Dave.
All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Are you using xl97?
http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Hi Dave,
No. I'm using xl2003. But it looks like it's doing just what you say if it was xl97. The boxes enter the data into the cells okay, but it seems like the VBA is not reconizing it as a Change. What's weird is, if I manually copy and paste the exact same text into the cells from the FillBox List, it works. Amy "Dave Peterson" wrote: Are you using xl97? http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
I assumed that you were using data|validation. But it sounds that you're using
either a dropdown from the Forms toolbar or a combobox from the control toolbox toolbar. (I'm not sure what a FillBox is.) And those worksheet change events don't fire these change. But each supports code that can be assigned to them. AMY Z. wrote: Hi Dave, No. I'm using xl2003. But it looks like it's doing just what you say if it was xl97. The boxes enter the data into the cells okay, but it seems like the VBA is not reconizing it as a Change. What's weird is, if I manually copy and paste the exact same text into the cells from the FillBox List, it works. Amy "Dave Peterson" wrote: Are you using xl97? http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Thanks Dave,
Sorry, I meant ListFillRange of the Combobox, not FillBox. I'm not going to give up on the code, because it works well. I guess I'll try to come with some kind of "reset" with a commandbutton after I use the drop down comboboxes to enter the text in the target cells. Maybe the Change event will recognize the new text then. Thanks for trying to help me. Amy "Dave Peterson" wrote: I assumed that you were using data|validation. But it sounds that you're using either a dropdown from the Forms toolbar or a combobox from the control toolbox toolbar. (I'm not sure what a FillBox is.) And those worksheet change events don't fire these change. But each supports code that can be assigned to them. AMY Z. wrote: Hi Dave, No. I'm using xl2003. But it looks like it's doing just what you say if it was xl97. The boxes enter the data into the cells okay, but it seems like the VBA is not reconizing it as a Change. What's weird is, if I manually copy and paste the exact same text into the cells from the FillBox List, it works. Amy "Dave Peterson" wrote: Are you using xl97? http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Or just use the combobox1.value instead of target.value in the _change event???
AMY Z. wrote: Thanks Dave, Sorry, I meant ListFillRange of the Combobox, not FillBox. I'm not going to give up on the code, because it works well. I guess I'll try to come with some kind of "reset" with a commandbutton after I use the drop down comboboxes to enter the text in the target cells. Maybe the Change event will recognize the new text then. Thanks for trying to help me. Amy "Dave Peterson" wrote: I assumed that you were using data|validation. But it sounds that you're using either a dropdown from the Forms toolbar or a combobox from the control toolbox toolbar. (I'm not sure what a FillBox is.) And those worksheet change events don't fire these change. But each supports code that can be assigned to them. AMY Z. wrote: Hi Dave, No. I'm using xl2003. But it looks like it's doing just what you say if it was xl97. The boxes enter the data into the cells okay, but it seems like the VBA is not reconizing it as a Change. What's weird is, if I manually copy and paste the exact same text into the cells from the FillBox List, it works. Amy "Dave Peterson" wrote: Are you using xl97? http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than one Target.Addresses
Thanks Dave,
I think I'm starting to get in a little over my head. I'll have to do some studying on targets and values with relation to the change event. I'm just learning. Thank you again, Amy "Dave Peterson" wrote: Or just use the combobox1.value instead of target.value in the _change event??? AMY Z. wrote: Thanks Dave, Sorry, I meant ListFillRange of the Combobox, not FillBox. I'm not going to give up on the code, because it works well. I guess I'll try to come with some kind of "reset" with a commandbutton after I use the drop down comboboxes to enter the text in the target cells. Maybe the Change event will recognize the new text then. Thanks for trying to help me. Amy "Dave Peterson" wrote: I assumed that you were using data|validation. But it sounds that you're using either a dropdown from the Forms toolbar or a combobox from the control toolbox toolbar. (I'm not sure what a FillBox is.) And those worksheet change events don't fire these change. But each supports code that can be assigned to them. AMY Z. wrote: Hi Dave, No. I'm using xl2003. But it looks like it's doing just what you say if it was xl97. The boxes enter the data into the cells okay, but it seems like the VBA is not reconizing it as a Change. What's weird is, if I manually copy and paste the exact same text into the cells from the FillBox List, it works. Amy "Dave Peterson" wrote: Are you using xl97? http://contextures.com/xlDataVal08.html#Change (Debra Dalgleish's site) AMY Z. wrote: Thanks Jim, Paul, and Dave. All three suggestions worked. Jim recommended using drop down boxes for the entries into A1 & B1. Ironically I was already using them. But.... If I use the Boxes to enter the text in either cell, it looks like the V.B.A. doesn't recognize the entry. But! if I manually copy the text from the Box Fill Lists and paste it into A1 & B1 the code works! Is there something behind the scene that is causing the VBA code to not recognize the cell contents when using the drop down box? Suggestions as to what may be happening is appreciated if you come back to this post. Thank you, Amy "Jim Cone" wrote: Hi Amy, The "target" is the range that was changed on the worksheet. The broad category this falls under is "events". There is only one target. However, that target could be one or more cells depending on how many were changed at the same time. One way to accomplish what you want is something like... If Target.Address = "$A$1" or If Target.Address = "$B$1" Then 'Do something However, the number of combinations that are possible start increasing rapidly. If you have 10 different words for A1 and 5 different words for B1 then you are faced with 50 possible pairs and your code could get very large. You may want to consider using two dropdown boxes, such that if the user selects "Apples" in box one then all of the apples varieties are displayed in box two. Debra Dalgleish has detailed instructions here... http://www.contextures.on.ca/xlDataVal02.html -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "AMY Z." wrote in message Hi, Jim Cone helped me with the code below a few months ago. It works great. What it does: It hides certain rows in Sheet2 If A1 in Sheet1 is a certain text word. Question: Is it possible to have more than one Target.Address? Example: If A1="Apples" and B1="Red Delicious" Then hide the specified rows in Sheet2. The working code I have now for a single Target.Address: 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 I can't find very much information on this Target.Address subject in my stack of books. I've tried different combinations but none of my own coding works so far. Thanks for your time in reading my question. Amy -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
| add target | Excel Programming | |||
| Target As Excel.Range or Target As Range | Excel Programming | |||
| Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range) | Excel Programming | |||
| How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming | |||
Linear Mode
