2nd UCase/Target Challenge

[joelle]

Pls how do I add a 2nd target [A2] to the code so that:
If A2 is equal to "V", unhide range "newrange",
else, hide "new range"

My current code:
If target A5 is <> blank, unhide superregion, else, hide it.

[vba]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, [A5]) Is Nothing Then Exit Sub

If UCase(Target) <> "" Then
Range("superregion").EntireRow.Hidden = False

Else
Range("superregion").EntireRow.Hidden = True
End If[/vba]
Its a challenge because I heard that we can only have one
private sub per ws, and I dont know how to expand the vba above
to hold one more target and condition (A2 = "V")

Your help is hugely appreciated.

Nee

[Jacob Hilderbrand]

Are you trying to do this?

[VBA] If UCase(Target) <> "" Or Range("A2") = "V" Then [/VBA]

[joelle]

[Quoting Jake]
Are you trying to do this?
If UCase(Target) <> "" Or Range ("A2") = "V" Then [/quote]

Hello Jake,
No.

Condition 1 : If A5 <> blank, unhide range "superregion"

Condition 2 is: independent, not an "OR", nor part of condition 1.
Its operation is like that of condition 1, but separate, because it looks at
another cell [A2] and another entry, then unhide yet another range (shown below).

If A2 = "V", unhide range "newrange"

My challenge: how do I get these 2 operations to roof under one Private Sub below since I cannot have 2 private subs?

[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, [A5]) Is Nothing Then Exit Sub
If UCase(Target) <> "" Then
Range("superregion").EntireRow.Hidden = False
Else
Range("superregion").EntireRow.Hidden = True
End If
[/VBA]

'as you can see right now, I only have one condition that looks
at entry to A5 only.

Many thanks for any help you can provide.
Nee

[Shazam]

Hi Nee,



There is a select case method to use but you want to see if 2 private subs could work in one worksheet module. See if this helps paste this code in the worksheet module.


[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Call ChangeEvent1(Target)
Call ChangeEvent2(Target)
End Sub

Private Sub ChangeEvent1(ByVal Target As Range)
If Intersect(Target, [A5]) Is Nothing Then Exit Sub

If UCase(Target) <> "" Then
Range("superregion").EntireRow.Hidden = False

End If
End Sub
Private Sub ChangeEvent2(ByVal Target As Range)

If Intersect(Target, [A2]) Is Nothing Then Exit Sub

If UCase(Target) = "V" Then
Range("superregion").EntireRow.Hidden = True
End If
End Sub
[/vba]

[joelle]

Hello Shazam,

Thank you for looking at my question and take the time to get me the code.
I ran it, and no, it does not work (like you said 2 private subs might work in one ws).
But I appreciate your kindness no matter.

I normally did not cross post, but under the gun for half a day, I went on another forum and I was so lucky to get the code below from another goodhearted exceller (he's "dcardno"), and I'd like to share it here. Hopefully, it does help another person that happens to run in the same challenge.

To me, this is a real vba challenge because I had never seen 2 UCase's (or targets) and 2 different conditions residing under one single Private Sub, which equals to 2 Private Subs in a WS -- which is, as I know, impossible, without some advanced twist.
Pls enjoy.

[vba]Credited to Dean (dcardno):
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, union([A2],[A5])) is Nothing Then Exit Sub
If Target.Address = [A2].Address Then
If [A2] = "V" Then
Range("newregion").EntireRow.Hidden = False
Else
Range("newregion").EntireRow.Hidden = True
End If
End If
If [A5] <> "" Then
Range("superregion").EntireRow.Hidden = False
Else
Range("superregion").EntireRow.Hidden = True
End If
End Sub
[/vba]
Best regards and thanks again for stopping at my post for
the effort to help.

Nee

[jindon]

Hi

If Target.Address = [A2].Address Then
If [A2] = "V" Then
Range("newregion").EntireRow.Hidden = False
Else
Range("newregion").EntireRow.Hidden = True
End If
End If

If I misunderstanding the problem, just ignore this post, but
I don't think you need

If Target.Address = [A2].Address Then

try:
select A1:A5 and hit delete key
you can hide "superregion", but not the other one..

CROSS POST
http://www.mrexcel.com/board2/viewto...=985718#985718