S
Steve
I've got three named ranges. I want to make sure the user selects exactly
one cell in each range before running a macro. The user should be able to
select the cells in any order. What's the best way to do this?
Here's my first try ("TimeY1", "TimeY2" and "TimeY3" are the named ranges on
the sheet where I want the user to make his selection). This works IF the
user selects the cells in the proper order (Col C, then F, then I) because
of the order of addresses in Selection.Address. If he selects them in any
other order, the Intersect doesn't work.
Any ideas how I can do this so the user can select the three cells in any
order?
Thanks,
Steve
Sub SolveForManHours()
Dim iRng As Range
If (TypeName(Selection) <> "Range") Or_
(Selection.Cells.Count <> 3) Or _
(Selection.Areas.Count <> 3) Then GoTo errorEnd
vAddr = Split(Application.Selection.Address, ",")
Set iRng = Intersect(Range("TimeY1"), Range(vAddr(0)))
If iRng Is Nothing Then GoTo errorEnd
Set iRng = Intersect(Range("TimeY2"), Range(vAddr(1)))
If iRng Is Nothing Then GoTo errorEnd
Set iRng = Intersect(Range("TimeY3"), Range(vAddr(2)))
If iRng Is Nothing Then GoTo errorEnd
' Process the cells here
Exit Sub
errorEnd:
MsgBox "Select one cell each in Col C, F and I", vbOKOnly, "Error"
End Sub
one cell in each range before running a macro. The user should be able to
select the cells in any order. What's the best way to do this?
Here's my first try ("TimeY1", "TimeY2" and "TimeY3" are the named ranges on
the sheet where I want the user to make his selection). This works IF the
user selects the cells in the proper order (Col C, then F, then I) because
of the order of addresses in Selection.Address. If he selects them in any
other order, the Intersect doesn't work.
Any ideas how I can do this so the user can select the three cells in any
order?
Thanks,
Steve
Sub SolveForManHours()
Dim iRng As Range
If (TypeName(Selection) <> "Range") Or_
(Selection.Cells.Count <> 3) Or _
(Selection.Areas.Count <> 3) Then GoTo errorEnd
vAddr = Split(Application.Selection.Address, ",")
Set iRng = Intersect(Range("TimeY1"), Range(vAddr(0)))
If iRng Is Nothing Then GoTo errorEnd
Set iRng = Intersect(Range("TimeY2"), Range(vAddr(1)))
If iRng Is Nothing Then GoTo errorEnd
Set iRng = Intersect(Range("TimeY3"), Range(vAddr(2)))
If iRng Is Nothing Then GoTo errorEnd
' Process the cells here
Exit Sub
errorEnd:
MsgBox "Select one cell each in Col C, F and I", vbOKOnly, "Error"
End Sub