Make Sure User Selects 3 Cells

  • Thread starter Thread starter Steve
  • Start date Start date
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
 
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?
Click to expand...

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]
 
Of course...so simple. Merci vilmal, Thomas.
Steve

Thomas Ramel said:
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?
Click to expand...

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]
Click to expand...
 
Grüezi Steve

Steve schrieb am 07.06.2004
Of course...so simple. Merci vilmal, Thomas.
Click to expand...

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

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

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