MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

pausing code

  Asked By: Rainhard    Date: Nov 28    Category: MS Office    Views: 1222

I am still having problems with pausing my code to let the
user make changes to the spreadsheet. The msgbox stops the code but
then I cannot change the spreadsheet. Using a userform with the
ShowModal = False allows me to change the spreadsheet but it does not
pause the code. Would really appreciate help as this would be the
easiest way to solve my problem.



6 Answers Found

Answer #1    Answered By: Kanchan Ap     Answered On: Nov 28

I don't think you'll be able to. You can't expect to be able to edit the
sheet while VBA is locked up.

Answer #2    Answered By: Haya Yoshida     Answered On: Nov 28

It sounds to me that you are going about this the wrong way, though this
is just an opinion based on the note I see here. Rather than thinking of
pausing code, you may want to think outside the box. Have your code  end
instead of pause.

For example, have a "User Form" appear. Via the user form have users
enter changes required to the spreadsheet. This can be done within the
form when, upon hitting a button on the user form changes will take
effect, and the program may be restarted.

Answer #3    Answered By: Geneva Morris     Answered On: Nov 28

I had thought about alternative ways to accomplish this. The two I came
up with was either making the userform  have the information placed in it
and then it updateing the spreadsheet; which was a little more time than
I want to put into this code.

The other and the one I was leaning to was as you said to have more code
connected to the command button on the userform that would complete my
code, but when I did that I ran into a problem  with a nested End Sub in
the main code. See, the userform is nested in an if then statement and
only appears under certain conditions therefore I need an End Sub in the
if statement to end the code. .... Idea just poped into my head. If I
have a GoTo function in the if statement can I jump to the end? Can
anyone help  with the syntax of a GoTo function in the Code? The help
mentions GoTo in the spreadsheet  and a ON...GoTo but not a GoTo in the

Answer #4    Answered By: Fabia Ferrrari     Answered On: Nov 28

Yes, a GoTo statement will push the program to where you want, however,
you should not need to continue code  that is already active in another
module. Simply close the form when finish what you need, i.e. update the
spreadsheet. When the form is closed... UserForm.Hide the program in
your main module will continue where it left off.

Answer #5    Answered By: Anuja Shah     Answered On: Nov 28

To stop the code, you don't use an "end sub" - that simply indicates the
bottom of the subroutine.

You can "exit sub" to leave the subroutine, or you can "end" to stop your
code completely.

Answer #6    Answered By: Emma Campbell     Answered On: Nov 28

You can use goto command as stated below;


goto reerun

For your first problem, as Craig Gross mention below you can use "User Form"
with ShowModal = False, further if you want, you can set the apperance similar
to msg box while disigning,

After the form activate event you can do what ever the changes you want to do
to the spreadsheet. (you can do this without any problem  since the ShowModal =

When, upon hitting a button on the user form (which you have disign like msb
box) the program will resume

Didn't find what you were looking for? Find more on pausing code Or get search suggestion and latest updates.