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

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

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

Pascal

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

I'm not often Naughty,

Ian

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

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