how to Pause a VBA procedure until user presses Enter

[Paul James]

Is there a way to suspend execution of a VBA procedure until the user takes
a specific action like pressing the Enter key?

Thanks in advance,

Paul

 

[Vasant Nanavati]

Only with some kind of InputBox or MessageBox.

 

[PC Datasheet]

You could put a loop in the middle of your code and check for the desired
specific action to exit the loop.

 

[Paul James]

Thanks for the reply, Vasant - Message Boxes won't work because I'm trying
to enable the user to perform some actions before the code resumes. With a
message box, the user can't perform any actions other than click a button on
the message box to make it go away, but then the code resumes as soon as the
message box closes, so I will have accomplished nothing.

With an Input Box, the same limitations prevail, except that you get to type
some text in the Input Box. Unfortunately, I need for the user to type more
than a small string of text. Sometimes several sentences. But again, I
want those sentenced typed at a very specific context, namely in the middle
of a procedure that does other specific things before and after the user
types those sentences.

Any ideas how to accomplish this?

Paul

 

[Paul James]

Thanks for your reply, PC.

I suppose I should have mentioned that I'd like the user to be able to do
some things in the host application while the VBA code is paused.
Specifically, type some data. (Input Boxes and Message Boxes won't work for
this, as I explained elsewhere in this threat).

If I create a loop that runs until the user takes some action, such as press
the Enter key, which is what I would like, the running VBA loop prevents the
user from doing anything else. So my user wouldn't be able to do anything,
and thus, nothing is accomplished.

In effect, I'd like to somehow momentarily interrupt (pause) execution of
the VBA code, while the user types several sentences of text, and then
resume the VBA code as soon as the user presses the Enter key.

Any ideas?

TIA

 

[Albert D. Kallal]

You most certanly can do this with a form...

Check out:

http://www.attcanada.net/~kallal.msn/Dialog/Index.html

 

[Greg Wilson]

Paul,

Can you not run the procedure and at the end prompt the
user to type the info into whichever cells and also set
the Enter key to run a second procedure using the OnKey
method? The second procedure would continue where the
first left off. At the end of the second procedure you
could reset the Enter key to normal using OnKey.

Alternatively, have the one procedure branch dependant on
the value of a module level or static variable. If the
variable equals 0 then run the first part and set it to 1
at the end of the first part. Then when the same procedure
is run again (by pressing the Enter key) it will run the
second part of the procedure. At the end of the second
part set the variable back to 0 and reset the Enter key to
normal.

Regards,
Greg

 

[Bob Phillips]

If you use Application.Inputbox, this operates in a way that allows further
action.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dreamer]

I remember reading somewhere that it's not possible to pause a macro
The solution for this is, as mentioned before, to make a loop and ru
it until the user does something..

 

[PC Datasheet]

Explaining further Albert's amd van's responses, create a pop-up form and open
it with code like this:
DoCmd.OpenForm "YourForm",,,,,acDialog

A button on your pop-up form then needs to make the form not visible. Clicking
this button will then cause the code following the OpenForm statement to resume.

 

[Paul James]

Thanks for the suggestion, Bob. However Application.InputBox still confines
you to entering any text in the InputBox without giving you the opportunity
to enter data elsewhere. I need more flexibility than that.

 

[Paul James]

You're right - a user form is the solution.

Duh.

Thanks, guys.

 

[Vic Eldridge]

Hi Paul,

The following example should suit your requirements.

Regards,
Vic Eldridge


Sub Macro1()
'Do some stuff...
Range("A1") = "Macro1 Started"
'Re-map the 2 Enter keys.
Application.OnKey "{Enter}", "EnterKeyPress"
Application.OnKey "~", "EnterKeyPress"
End Sub

Sub EnterKeyPress()
'Reset the Enter keys to normal behaviour.
Application.OnKey "{Enter}"
Application.OnKey "~"
Macro2
End Sub

Sub Macro2()
'Do some more stuff...
Range("A1") = "Macro2 Started"
End Sub

 

[Paul James]

I never would have thought of remapping the Enter key, then using the
remapping to start the remaining procedure. Very clever.

Thanks, Vic.

Paul

 

[Vic Eldridge]

You're right - a user form is the solution.

Not necessarily.
See my reply to your original post.

Regards,
Vic Eldridge

 

[Paul James]

Vic - I've got a question about the solution of redefining the Enter key.

If all goes as expected, it should work fine. However, if the application
closes for any reason before the user presses the Enter key, thus restoring
the original definition, what happens the next time the Enter key is
pressed - does it return to the default definition?