How can I select a range manually with the mouse?

[ian]

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

 

[papou]

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

 

[papou]

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

 

[ian]

Pascal

Thanks. Never used an Application.Inputbox before. Always easy when
you know how.

I'm not often Naughty,

Ian

 

[dan dungan]

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.

 

[dan dungan]

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

 

[Basilisk96]

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

 

[papou]

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]

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

 

[papou]

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

 

[dan dungan]

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