Need simple help with multiple ranges, array maybe?
Posted by Beginner Bob on December 10, 2001 3:41 PM
I'm not very familiar with the array or intersect commands, and I don't even know if that's what I need, but here's what I have so far:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim ASH As Worksheet
Set ASH = ActiveSheet
If UCase(Left(ASH.Name, 3)) = "STD" Then
If Target.Column > 2 And Target.Column < 7 Then
If Not Target.Row = 6 And Not Target.Row = 13 And Not Target.Row = 17 Then 'many more rows here
UserForm4.Show
End If
End If
End Sub
Basically I would like to show the userform if the activecell is in columns 3 through 5, and is not in rows 6, 13, or 17. Actually I have about 12 rows, but this is simplified, and I need to simplify my code. Any help is greatly appreciated.
Posted by Juan Pablo G. on December 10, 2001 4:37 PM
Try with something like
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim NotRng As Range
Dim YesRng As Range
Dim ASH As Worksheet
Set ASH = ActiveSheet
If UCase(Left(ASH.Name, 3)) = "STD" Then
Set NotRng = Union(Rows(6), Rows(13), Rows(17))
Set YesRng = Range("C:E")
If Not Intersect(Target, YesRng) Is Nothing And Intersect(Target, NotRng) Is Nothing Then
UserForm4.Show
End If
End If
End Sub
Juan Pablo G.
Posted by Beginner Bob on December 10, 2001 5:38 PM
That's perfect, thanks Juan.
I added my other rows, and I'm good to go, thanks.
