Can VBA tell a user a macro is running until it ends?

[Guest]

I am currently writing VBA code to refresh and update an Excel Workbook with
many queries and pivot tables - thus, it takes a few minutes.

Does anyone know if a Message Box can be displayed telling the user that the
Macro is running, and then automatically diasappear after it closes???

Also, I've heard of "Splash Screens" but cannot find any information in the
VBA Help, Microsoft Office Online, or in my VBA books. Does anyone know
anything about these?

This Macro will be used by many people at my office, so some do not always
have the Status Bar displayed which is why I would like to use the MsgBox.

Thanks for your help!

 

[Bob Phillips]

I am currently writing VBA code to refresh and update an Excel Workbook with
many queries and pivot tables - thus, it takes a few minutes.

Does anyone know if a Message Box can be displayed telling the user that the
Macro is running, and then automatically diasappear after it closes???

You could use a progress bar. See
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

Also, I've heard of "Splash Screens" but cannot find any information in the
VBA Help, Microsoft Office Online, or in my VBA books. Does anyone know
anything about these?

See http://xldynamic.com/source/xld.xlFAQ0007.html

 

[witek]

I am currently writing VBA code to refresh and update an Excel Workbook with
many queries and pivot tables - thus, it takes a few minutes.

Does anyone know if a Message Box can be displayed telling the user that the
Macro is running, and then automatically diasappear after it closes???

Also, I've heard of "Splash Screens" but cannot find any information in the
VBA Help, Microsoft Office Online, or in my VBA books. Does anyone know
anything about these?

This Macro will be used by many people at my office, so some do not always
have the Status Bar displayed which is why I would like to use the MsgBox.

Thanks for your help!

create userform and set ShowModal to false


below is a code to show how to use it.
lblMessage is a lable on it.



Load frmWait
frmWait.lblMessage = "Please wait..."
frmWait.Show
DoEvents

.... your job here ....



unload frmWait

 

[KR]

In addition to the other excellent suggestions, I sometimes use the
statusbar to show enough information for the user to know the program is
still running, for example, if I am looping through 10,000 records, I might
use something like

For i = 1 to 10,000
Application.Statusbar= "Calculating record " & cstr(i) & " of 10,000"
'code to actually process each record
Next i

Then just before end sub, you need to clear the statusbar using:
Application.statusbar = false

HTH, Keith