Either use a
MsgBox to ask for the user input, or set your form to be
Modal (MSDN link).
A Modal form will allow you to continue to work in the form, and any code underlying the form will continue to run, but the main code that calls the form will be 'blocked'.
Should the underlying Excel sheets be modified during the period of pause, you can add code when the button press is detected (i.e. when the Form is closed or then the MsgBox
OK button is pressed) to update the relevant parts of the Form.
But we have digressed from the original simple question and many other factors (such as how the underlying sheets will be modified when the form is open) come into play.
- Consider if a change in the underlying sheet has to change the form immediately or if it can occur when the user resumes the form/code.
- Consider if the functions you want can be done natively in the spreadsheet and minimise the use/functionality of the form.
- Consider other event driven options where the code is broken into natural sections, functions, subroutines or modules and each section only runs when triggered by the right event (sheet change, user input, subroutine finished, form exit etc.)