pause vba code until user pastes values in spreadsheet

[JCIrish]

In my code when a user opens a worksheet I show him a MsgBox asking him if he
is ready to paste the clipboard contents into a certain cell on the
worksheet. The code that executes after the values have been pasted sorts the
values the user has pasted.
The problem is that the sort code executes as soon as the user clicks "yes"
to dismiss the MsgBox, thereby not allowing him an opportunity to perform the
paste operation.
How can I pause the code when the MsgBox is dismissed so that the user can
paste in the values from the clipboard, and then have the code resume
execution after the paste operation has been completed?
All help will be much appreciated.
Or, is there a better way to code for this sequence of events?

 

[Mike H]

Hi,

Without seeing you code then it's difficult but in principle you can split
things into 3 modules.

1. Display your message box. The user can click yes or no and nothing
happens until the target cell for the paste changes.

2. This worksheet change code which performs the sort. I used A1 as an
example but this can be anything. it's all a bit marginal in truth because
you can't dictate where the user will paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
'Your sort code
Call yourmacro
End If
End Sub

and then
3 You macro which is called by the change event code.

Mike

 

[JCIrish]

Thanks, Mike. Sorry that I haven't responded sooner. I've been away from my
desk. (and for some reason I didn't get an e-mail about your post). I should
have enclosed a snippet of the code. But, I think you've given me a solution
to the problem. I'll try that route and get back to you if I need to. Again,
thanks for your help.

jcirish