Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause vba code until user pastes values in spreadsheet
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause vba code until user pastes values in spreadsheet
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause vba code until user pastes values in spreadsheet
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 "Mike H" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet pastes as picture | Excel Discussion (Misc queries) | |||
Code that searches a column, then copies and pastes any matches intoa new Spreadsheet | Excel Programming | |||
Pause a macro for user input | Excel Programming | |||
Pause VB Code to Allow User Input on Worksheet | Excel Programming | |||
change a macro so that it copies and pastes data as values rather than references | Excel Programming |