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

Only with some kind of InputBox or MessageBox.

--

Vasant

"Paul James" wrote in message
...
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

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

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"Paul James" wrote in message
...
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

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

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

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

Check out:

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

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


-----Original Message-----
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


.

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)

"Paul James" wrote in message
...
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

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..

--
Message posted from http://www.ExcelForum.com

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"Paul James" wrote in message
...
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

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.


"Bob Phillips" wrote in message
...
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)

"Paul James" wrote in message
...
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

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

Duh.

Thanks, guys.

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" wrote in message ...
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.


"Bob Phillips" wrote in message
...
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)

"Paul James" wrote in message
...
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

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

Thanks, Vic.

Paul

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

Not necessarily.
See my reply to your original post.

Regards,
Vic Eldridge

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?