Pause macro for user cell address input

[Ed Haslam]

Say I write a VB macro to copy and paste some data. But
each time I need to paste the data in a slightly different
location. I would like the macro to pause for me to
select a target cell and then press [enter](or do the same
with a mouse), at which point the macro would continue
running.

Thanks, Ed

P.S. I think this was a question mark surrounded by
brackets {?} in Lotus 123.

 

[steve]

Ed

Found this using Google Search (don't know what version you are using
but this might help) (later versions of Excel support modeless forms for
selecting)

However, Stephen Bullen has developed code that allows you to have modeless
userforms in Excel 97. Download FormFun.xls from his site:

http://www.bmsltd.co.uk/Excel/Default.htm

 

[Chip Pearson]

Ed,

Use Application.InputBox with a Type value of 8. This will allow the user to
select a range. E.g.,

Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox(prompt:="Choose A Range", Type:=8)
If Rng Is Nothing Then
' user didn't select anything
Else
MsgBox "You selected " & Rng.Address
End If