Select cell during runtime and continue

[james.rees02]

Hi - am developing a macro for users to use and need help refining one part of it.
Essentially, the user selects a cell, then clicks a button to run a macro. This is the "From" cell. The macro then needs a "To" cell address to be able to complete. I have got it working using the following code:
Dim RngTo As Range
Set RngTo = Application.InputBox("select cell", Type:=8)

but it opens up an input box with OK/Cancel buttons......where the user selects the cell then clicks OK.

Ideally, what I'd like to see is the macro continuing as soon as the user clicks the cell.....removing the need to click OK....(small step I know but I'm like that )

What is the code that would enable this. Any help is appreciated.

Thanks.

 

[drsarao]

James,
Not possible with InputBox. (It is logical too. How would InputBox know, when you have finished selecting!)

You can try:
Let user select BOTH "From and "To" cells using Ctrl+Click (need to be on same sheet). Then click the macro button.
In the macro use Selection.Address, Left/Mid to parse the 2 addresses.
Thus:
tmp = Selection.Address
Set rngfrom = Range(Left(tmp, InStr(tmp, ",") - 1))
Set rngTo = Range(Mid(tmp, InStr(tmp, ",") + 1, Len(tmp)))

HTH