You should be able display the form as vbModeless
and only execute code when specifically requested, i.e., from a CommandButton
or other control.
You then leave the form visible/shown until it is specifically closed, via the "X" button or via another control which calls the UserForm_Terminate
event.
In order to achieve this, you may need to move some of your executable code in to another subroutine and/or module, and call this subroutine for example from a CommandButton_Click
event.
You already have a subroutine somewhere that contains a line like:
Sub ShowTheForm()
UserForm1.Show vbModeless
End Sub
So the form is displayed properly to allow user-input to the parent application.
You don't really need to put any other code in the above module. We will put the other code in other modules/subs, and then call it from user controls like command buttons.
Example:
Take all of your executable code, and put it in another subroutine (and if it suits your organizational preference, another module), like:
Sub MyMacro(msg$)
MsgBox msg
End Sub
On the UserForm, add a command button and assign it the following code:
Sub CommandButton1_Click()
MyMacro "hello"
End Sub
Now, the form will display until the user clicks the "X" button. Code will only run when called from the command button.
EDIT FOR CLARIFICATION
You don't need to "pause" the execution using this method. Execution ends once the form is displayed modelessly, and the form persists. The object has some events which you may use to trigger further execution of code.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…