Pause macro execution while file is open

[mikjp]

OK - here is the scenario:

Main macro opens a selected workbook to be 'worked on'.

This workbook is then closed by the user using the standard 'X' (NB Not by a macro code line)

I want to suspend macro execution in the main macro while the new workbook is 'worked on' until the new workbook is closed and saved, and then continue it (it basically re-opens an updated UI).

In the 'This workbook' section of the 'new' workbook to open I have:

Sub Workbook AfterSave(ByVal Success As Boolean)
If Success = True Then
ActiveWorkbook.Close SaveChanges:=True
???
End If

What I cannot seem to do is get the main macro to continue after the 'new' workbook is closed and saved. Even if I try to run a new macro in the main section to execute the desired actions I am told (correctly, Excel 2016) I cannot open two versions of the same workbook.

Is there anything I can add to the Workbook AfterSave code in place of ??? (or in the main macro) to do this?

 

[JLGWhiz]

You don't have to open the workbook again to run a second macro. As long as your application(Excel in this case) is still active, a second macro can be initiated to complete whatever you wanted done on the active workbook. You can write the macro, put it in any one of the numered code modules that you have to insert and attach it to a button for the user to click when they have finished their manual updates. But VBA is not designed to pause a macro so that worksheets can be edited. You can write your macros to run in segments that you manually start and stop.

You might find some code on line that attempts to pause a macro and allow editing, but it is cumbersome and in most cases unreliable. It is best to do your macros in phases by using multiple macros if you require manual editing at certain points in the code. Actually, the best way to do it is to use VBA to do your editing.

 

[mikjp]

Thanks, jlg - I guess I was being over-ambitious in trying to get the user to close with the 'X' to avoid cluttering the screen with another button

Clarification appreciated.

 

[JLGWhiz]

Thanks, jlg - I guess I was being over-ambitious in trying to get the user to close with the 'X' to avoid cluttering the screen with another button

Clarification appreciated.

Happy to help,
regards, JLG

 

[mikjp]

jlg - if I may tap your knowledge base again? Is there no way in VBA to stop execution of a macro while waiting for an event WITHOUT the loop/while-wend routines? I find that very disappointing and those routines just sap the energy in the programme and give me a fuzzy little spinning wheel. It is easy enough to return a value with aftersave and then to halt the macro waiting for a change in that return - heaven!!

 

[mikjp]

I appreciate this is sacrilege and I may well be tarred and feathered for asking, but do you know if an event listener in Libre Office would do the trick?

 

[JLGWhiz]

There are ways to edit a worksheet while a macro is active. But you cannot pause a macro to relinquish control to the user and then resume the macro where it left off. As stated earlier, you might find some code using non-modal UserForms that have limited capability to do edits and resume the macro, but the code is cumbersome and not recommended for the novice user. As for the While-Wend loops, I could not respond to that without knowing how it is being applied. The best way to handle editing is to plan ahead. If you can envision a need for edit before running the code, then build the edit into the code, using If...Then statements to test the conditions and initiate changes where criteria dictates. But without specific conditions being given, it is difficult to offer any specific advice on how to cope with the issue.

 

[JLGWhiz]

In case you are still looking. Here is a method that I use for editing while the macro is active. The Application.InputBox allows you to select a cell to edit with your mouse pointer. If the dialog box blocks your view of the worksheet, you can use the mouse pointer to move the box out of the way so you can select the cell you want to edit. Do not attempt to type in the cell, just click on it and then click the OK button when that cell address appears in the dialog box. The second InputBox then appears for you to enter the value you want, click OK and the cell will be populated with that value. You can either put this in a standard code module and call it from another macro when needed, which is how I have it, or you can build gist of the code into a host macro. In any case, you will need to use something like a Select Case or If...Then statement to set the conditions for when the macro will be used. This is only one method of in process editing. You can also use ListBoxes, ComboBoxes and TextBoxes, all requiring appropriate code to specify the circumstance for which the edit will be necessary. This is the simplest of those type methods.

Sub RunTimeEdit() 'For editing while macro is running
Dim adr As Range, inpt As Variant
Set adr = Application.InputBox("Select Range to Edit", "RANGE TO EDIT", Type:=8)
inpt = InputBox("Type What You Want To See In The Cell", "CELL ENTRY")
adr = inpt
End Sub

 

[mikjp]

Thanks again, JLG - very useful.