0

I have an Excel workbook macro that opens another workbook and starts copying data into it and formatting it. At one point in the process, I want the macro to pause and let the user manually enter data into the target workbook and then resume processing.

MsgBox, Application.Wait(), and Sleep are all application modal and will not let the user update anything in the other workbook while they are executing.

I found this while searching for a solution. It gets me halfway there in that I can manipulate the other sheet but only with my mouse. No keyboard presses get sent to the workbook.

Any ideas on getting all the way there?

I was thinking that I could just have two macros. The user would run one, then perform his manual tasks, then run the other. This appears to work but I would have to convert everything to globals so hopefully, someone has a better idea.

Thanks!

Occams Stubble
  • 37
  • 8
  • You can set a [breakpoint](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/set-and-clear-a-breakpoint). – Tehscript May 15 '18 at 16:46
  • Looks very much like an X-Y problem. You need to restructure your stuff. Hard to tell without seeing the code or having the slightest idea of what your code does. But yeah, sounds like you want two macros. Or bring up a UserForm for the user to provide input. – Mathieu Guindon May 15 '18 at 17:26
  • 1
    Add `DoEvents` to your current code and then trap keyboard events in separate new code, see this https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – S Meaden May 15 '18 at 19:08
  • 2
    If you raise an Userform, the macro will stop, and will resume when the Userform is closed. Another option would be raising an INPUTBOX, (because it will pauses the execution until user inputs something), that ask user for the values you need to type manually and where. So if you need to input something simple and easy, use the Inputbox. If it is something more complex and you want to have total control of what Excel does during the pause. use an Userform. – Foxfire And Burns And Burns May 15 '18 at 19:33

1 Answers1

-1

Depending on the macro being run to copy and paste, is the main concern with user intervention during execution of the macro getting the active cell/sheet (if being used) back to being active after the user manipulates something.

I'd recommend storing the active cell/sheet address in a variable prior to the Application.Wait() and then setting the active cell to that stored value on resume.

Without a posting of what your macro is doing though, it is hard to know if this suggestion helps your current situation.

JS20'07'11
  • 309
  • 3
  • 7
  • The sheet has a list of tasks along with the estimated time to complete and the progress so far. I have a column for percent complete which is easy to calculate. The problem is that some tasks are split between two people. The task names are like "XYZ (John)" and "XYZ (Jane)" I need to calculate the overall percent complete between them. If it's a total of ten hours and John has done 5 and Jane has done 2 then the percent complete is 100 for John and 40 for Jane but the overall percent complete is 70 on both lines. I tried doing it with a pivot but it did not work. – Occams Stubble May 15 '18 at 18:45
  • The project and name fields need to both be used to calculate the percent complete. This can be done with a set of sumifs. It is usually easiest to do this by splitting the columns such that you have one column with XYZ and the next column with the username. Then have a column for planned project hours, a column for username % of planned project hours, and a column with usernames completed hours. – JS20'07'11 May 17 '18 at 14:25