Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
I like a macro which gives me control to select a range in a worksheet
manually, then continues with the macro. I'd hoped to have a message box with something like "Select the range to process" which I select off the worksheet with my mouse and then I click ok to continue. I've googled and can'd find any pointers. Can anyone help please? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Hello Ian
Use Application.Inputbox: Dim MyRangeRef As Range On Error GoTo Select_Cncled Set MyRangeRef = Application.InputBox("Select the range of cells", "Reference?", , , , , , 8) Exit Sub Select_Cncled: MsgBox "you cancelled the previous message, you naughty!" HTH Cordially Pascal "ian" a écrit dans le message de news: ... I like a macro which gives me control to select a range in a worksheet manually, then continues with the macro. I'd hoped to have a message box with something like "Select the range to process" which I select off the worksheet with my mouse and then I click ok to continue. I've googled and can'd find any pointers. Can anyone help please? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Even if it seems obvious, let me just indicate that you will then be able to
use the range for further actions before the "Exit Sub", eg: Dim MyRangeRef As Range On Error GoTo Select_Cncled Set MyRangeRef = Application.InputBox("Select the range of cells", "Reference?", , , , , , 8) MsgBox MyRangeRef.Cells.Count 'do other stuff 'etc. Exit Sub Select_Cncled: MsgBox "you cancelled the previous message, you naughty!" HTH Cordially Pascal "papou" a écrit dans le message de news: ... Hello Ian Use Application.Inputbox: Dim MyRangeRef As Range On Error GoTo Select_Cncled Set MyRangeRef = Application.InputBox("Select the range of cells", "Reference?", , , , , , 8) Exit Sub Select_Cncled: MsgBox "you cancelled the previous message, you naughty!" HTH Cordially Pascal "ian" a écrit dans le message de news: ... I like a macro which gives me control to select a range in a worksheet manually, then continues with the macro. I'd hoped to have a message box with something like "Select the range to process" which I select off the worksheet with my mouse and then I click ok to continue. I've googled and can'd find any pointers. Can anyone help please? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Pascal
Thanks. Never used an Application.Inputbox before. Always easy when you know how. I'm not often Naughty, Ian |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Hi,
The error handling didn't work for me using excel 2000 on xp. If I didn't select a range and clicked ok, the procedure returned the Dialog box-- The formula you typed contains an error. On Oct 25, 3:24 pm, ian wrote: Pascal Thanks. Never used an Application.Inputbox before. Always easy when you know how. I'm not often Naughty, Ian |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Sorry I sent the last message before I was finished typing.
Anyway, If I choose the cancel button, the procedure returns runtime error 424--object required. Also, I could never get the procedure to go to the error handler. Please help me understand the error handling. Thanks, Dan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
dan,
For completeness' sake, are you using something like this? Sub GetCustomRange() Dim MyRangeRef As Range On Error GoTo Select_Cncled Set MyRangeRef = Application.InputBox("Select the range of cells", "Reference?", , , , , , 8) Exit Sub Select_Cncled: MsgBox "you cancelled the previous message, you naughty!" End Sub I'm not sure that there is a way to catch the "The formula you typed contains an error" issue - since it does not seem to be an error that fires an event back to VBA; it's simply a warning to the end user, sort of saying, "Give me something to work with before you press OK, dummy!" :-) Error 424, on the other hand, is generated because upon pressing Cancel, the input box is destroyed and the above code does not assign the expected object to MyRangeRef; therefore, VBA complains by raising a catchable error, to which the error handles courteously responds. Learning something new every day, -Basilisk96 On Oct 25, 8:27 pm, dan dungan wrote: Sorry I sent the last message before I was finished typing. Anyway, If I choose the cancel button, the procedure returns runtime error 424--object required. Also, I could never get the procedure to go to the error handler. Please help me understand the error handling. Thanks, Dan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Hello dan
If you do not give the expected value (Cell reference) and choose OK , the input box method will still show with a Excel error message indicating a wrong input. This is a specific behaviour attached to the Application.Inputbox method (different from the Inputbox function) See help on Inputbox method for further information. The error handler is valid (and functional) if you choose the Cancel button with the sample code supplied. HTH Cordially Pascal "dan dungan" a écrit dans le message de news: ... Sorry I sent the last message before I was finished typing. Anyway, If I choose the cancel button, the procedure returns runtime error 424--object required. Also, I could never get the procedure to go to the error handler. Please help me understand the error handling. Thanks, Dan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Hi Pascal,
I don't know what I'm doing that causes the procedure not to return the Select_Cncled error message. I copied the procedure to the sheet3 module and I run the macro from the tools/macro menu. When I click on the Cancel button the procedure returns the Visual Basic Debug message box showing run time error '424'. Here's what is in the module: Sub Test2() Dim MyRangeRef As Range On Error GoTo Select_Cncled Set MyRangeRef = Application.InputBox("Select the range of cells", "Reference?", , , , , , 8) MsgBox MyRangeRef.Cells.Count 'do other stuff 'etc. Exit Sub Select_Cncled: MsgBox "you cancelled the previous message, you naughty!" End Sub The error handler is valid (and functional) if you choose the Cancel button with the sample code supplied. HTH Cordially Pascal |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I select a range manually with the mouse?
Hi Pascal,
I think it was Chip Pearson that told me because I had selected in the VBE under tools--options--general tab--Break on All Errors would produce this behavior. He recommended I change the selection to Break in Class Module Dan On Oct 30, 1:30 am, "papou" wrote: Hi dan The code would be better placed in a standard module. But even if placed in a sheet module there should be no reason for its not working. Just tested on my version (Excel 2003) HTH Cordially Pascal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select a range of cells in an Excel table using the mouse | Excel Discussion (Misc queries) | |||
Not able to select range of cells with mouse. | New Users to Excel | |||
Cannot select single cell. Mouse move automatically creates range. | Excel Discussion (Misc queries) | |||
Code to Select a Combobox with having to Manually Mouse or Tab to enter a value | Excel Programming | |||
Mouse sticks in select range mode | Excel Discussion (Misc queries) |