More than one Target.Addresses

G

Guest

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
 
J

Jim Cone

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." <[email protected]>
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
 
P

PCLIVE

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
 
D

Dave Peterson

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
 
G

Guest

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
 
G

Guest

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
 
D

Dave Peterson

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.
 
G

Guest

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
 
D

Dave Peterson

Or just use the combobox1.value instead of target.value in the _change event???
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
Click to expand...
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reset to full rows visible 5
Hide Next row based on Cell Value 3
macro 2
Hide/Show Rows based on Cell Value with Data Validation 3
Worksheet_Change 3
Hide rows using VBA based on cell value 0
Macro help 4
Hiding / unhiding rows based on change of a cell 8

Top