Make Sure User Selects 3 Cells
 

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

Make Sure User Selects 3 Cells
 

Grüezi Steve

Steve schrieb am 07.06.2004

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

Any ideas how I can do this so the user can select the three cells in any
order?

Use the Selection itself to compare with your named Ranges:

Sub SolveForManHours()
Dim iRng As Range

If (TypeName(Selection) < "Range") Or _
(Selection.Cells.Count < 3) Or _
(Selection.Areas.Count < 3) Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY1"), Selection)
If iRng Is Nothing Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY2"), Selection)
If iRng Is Nothing Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY3"), Selection)
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

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

Make Sure User Selects 3 Cells
 

Of course...so simple. Merci vilmal, Thomas.
Steve

"Thomas Ramel" wrote in message
...
Grüezi Steve

Steve schrieb am 07.06.2004

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

Any ideas how I can do this so the user can select the three cells in
any
order?

Use the Selection itself to compare with your named Ranges:

Sub SolveForManHours()
Dim iRng As Range

If (TypeName(Selection) < "Range") Or _
(Selection.Cells.Count < 3) Or _
(Selection.Areas.Count < 3) Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY1"), Selection)
If iRng Is Nothing Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY2"), Selection)
If iRng Is Nothing Then GoTo errorEnd

Set iRng = Intersect(Range("TimeY3"), Selection)
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

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

Make Sure User Selects 3 Cells
 

Grüezi Steve

Steve schrieb am 07.06.2004

Of course...so simple. Merci vilmal, Thomas.

Your'e welcome - and thank you for the feedback.

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]