Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort by name when user selects name on another worksheet | Excel Discussion (Misc queries) | |||
Need an input box when user selects a value in a drop down box | Excel Worksheet Functions | |||
Start Macro after user selects a choice from a pick list | Excel Discussion (Misc queries) | |||
User selects a range | Excel Programming | |||
msgbox prompt when user selects data from combo box | Excel Programming |