Popup a Msg to Prevent Code Interruption

  • Thread starter Thread starter Ricky Pang
  • Start date Start date
R

Ricky Pang

Hello Experts,
I have a macro that's rather long. In order to run it faster, I turned
off the screenupdating. But, if another user activates the macro,
nothing happens on-screen so they attempt to click on the macro button a
few more times. That interrupts the macro which is running in the
background.

How do you write an on-screen message in code that says "Please wait
While the Macro is Running"? And how do you take it off? I would take
the msg off just after I turn the screenupdating back On.

I'm looking for a msg box only without the "OK" button.

Thanks in advance,
Ricky
 
Ricky,

If you are in XL97, have a look at using application.statusbar in the help
file. If you are in xl2000 or higher, create a custom userform with only a
label on it that says "Macro running..." and show it as modeless.

In your code
My Sub()
frmWait.show(vbmodeless)
'run code here
unload frmWait
End Sub

Alternatively, for a more elegant solution there is a progress bar class on
my site (which does the same thing but with progress indicators) at
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm. If you use
it, for your purposes, just remove the Cancel button and related code for
the form.

Yours,

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in
 
Thanks Robin,
Three questions.
1) How do you show a custom userform as modeless?
2) I have inserted a Userform, and entered the label as "Please wait
while Macro is Running". But, I get an error message when I incorporate
your code "frmWait.show(vbmodeless)..." So how do I incorporate my
userform into your code?
3) I've also looked into your Progress Bar. It's a good tool. I have
noticed that I could still click while the macro is running. So, what
if disabling the keyboard and mouse function while the macro is running
might be another alternative? How would you disable it?

Much appreciated,
Ricky
 
Ricky.

1 & 2. Sorry, my mistake. It's frmWait.Show vbmodeless. ie. without the
brackets shown in my original response. Note, you need to be in XL2000 or
higher for this to work.

3. Yes, you can still click on the progress bar cancel button when the macro
is running, which raises an event that ends execution of everything. It's
crude but effective if something is taking too long to run - and could have
unintended consequences if you aren't careful. If you don't want the user to
be able to do that and you are going to use the progress bar class, you can
just add one line which disables the ability to cancel. If you look at the
Demo module in the example workbook the code would then look like this...

PB.Title = "This appears at the top of the bar"
PB.Caption1 = "This appears on the first text line"

PB.DisableCancel = True 'ADDED THIS LINE
PB.Show

I don't think you need to disable the keyboard or mouse.

Hope that helps,

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in
 
Hi Robin,
When I added Userform1.Show vbModeless as the first line of my codes,
the Userform pops up but my codes just won't execute in the background
unless I click the X in the corner to close it first.

How can I have the userform show while my codes execute in the
background?

I do like the progress bar. I notice that it counts to 100 but does
this progress bar actually measure length of my code and increments
according to what has actually been completed?

Thanks again,
Ricky
 
Back
Top