MsgBox question

[Ken Loomis]

I run a routine that can take quite some time. I'd like to present a MsgBox
at various stages of this operation, in case the user is sitting there
watching.

But, I'd like the message box to go away on it's own to let the process
continue even if the user isn't there to answer.

For example, when all the files that need to be processed have been found,
I'd like a message box to appear that says something like:

"22 files that need to be processed have been found. Click OK to continue
processing these files. Or, just wait for this message to disappear."

This way the user can start the process and go to lunch or whatever and it
will still complete.

TIA,
Ken

 

[Nick Hodge]

Ken

You will not be able to use a standard msgbox for this as forms and messages
in XL are modal (*Forms* from XL2000 up can be set as non-modal). This means
code will not continue to execute until they are dismissed

The easiest way to do this, although not great is via the status bar. This
can be updated at various stages throughout the code and set to False at the
end to set it back to 'Ready' You may want to use one msgbox to advise
users that status will be shown bottom left on the status bar

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Bob Phillips]

Ken,

There are details of a timed Msgbox at
http://www.xldynamic.com/source/xld.xlFAQ0022.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Nick Hodge]

Bob

I'm not doing too well tonight. Misread that he wanted to advise user of
progress. Not seen the scripting msgbox before but apart from the OPs
opening sentence it seems the answer lies in your post

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Bob Phillips]

Hi Nick,

One has to ask though that if the Msgbox is not important, being closed
without even being seen, why put it up at all? If he just wants to re-assure
a watching user, progress bars of whatever form seem better.

Bob

 

[Chip Pearson]

Ken,

In VBA, go to the Tools menu, choose References, then choose
"Windows Script Host Object Model". Then, use code like the
following:

Dim WSH As IWshRuntimeLibrary.WshShell
Dim Res As Long
Set WSH = New IWshRuntimeLibrary.WshShell
Res = WSH.Popup(Text:="Your Text", secondstowait:=5, _
Title:="Title", Type:=vbYesNo)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com