Pausing Code

[Nigel]

Hi All
I currently have an application code that uses a series of MsgBox to ask
user to accept or not specific actions. Such as "Data for Dec07 is already
loaded, overwrite?", a Yes / No option then determines the next step.
This works ok.

I have now decided to incorporate a UserForm, with labels acting as messages
and command buttons to decide actions. Unlike the MsgBox which halts the
code this method does not pause. Rather than have to re-engineer the entire
code, I was looking for suggestions as to how to proceed.

 

[Chip Pearson]

Nigel,

A user form can be displayed modally or modelessly. When it is displayed
modally, with code like

UserForm1.Show vbModal

the code pauses on the Show method and only code within the form can
execute. You cannot access the worksheet or command bars while the form is
visible. When the form is hidden or unloaded, code resume execution on the
line following the Show method.

When a form is displayed modelessly, with code like

UserForm1.Show vbModeless

the Show method causes the form to be displayed but execution carries on
after the Show even while the form is visible. If you don't specifiy vbModal
or vbModeless, the default mode is based on the ShowModal property of the
form.

I would explicitly use vbModal to ensure that the form is being displayed
modally.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Nigel]

Thanks Chip, your suggestion which is proposing simulating a MsgBox using a
custom UserForm. Which is OK, but what I am trying to do is Open a
UserForm, keep it open, and have a message centre updating users on
progress, halting the code when an interaction is required, enabling some
controls for the user to interact with for the code to either progress or
cancel.

I think I will need to change control of the code to the UserForm and create
a series of subroutines / functions to execute the tasks, I am just going to
have to bite the bullet and being re-engineering.

Many thanks for your guidance.

--

Regards,
Nigel
(e-mail address removed)

 

[SteveM]

Thanks Chip, your suggestion which is proposing simulating a MsgBox using a
custom UserForm. Which is OK, but what I am trying to do is Open a
UserForm, keep it open, and have a message centre updating users on
progress, halting the code when an interaction is required, enabling some
controls for the user to interact with for the code to either progress or
cancel.

I think I will need to change control of the code to the UserForm and create
a series of subroutines / functions to execute the tasks, I am just going to
have to bite the bullet and being re-engineering.

Many thanks for your guidance.

--

Regards,
Nigel
(e-mail address removed)

Nigel,

To follow up on Chip's advice. I'm more of a hacker rather than a
programmer because I code to create interfaces for mathematical
modeling. But I do something similar to what you are thinking by
creating an interface form and showing it modally. But then conduct
the background work by setting Application.ScreenUpdating = False
after presenting to form to the user. So it looks modal even though
it isn't. And even with screen updating off, form objects will
continue to update based on events happening in background. So you
can show/hide, activate/deactivate various controls as needed until
the activity sequence is completed. So that's one way to do it.

Good Luck,

SteveM