Solved

Message in Excel VBA

Posted on 2014-01-03
7
384 Views
Last Modified: 2014-01-03
I want to show a message as below when users hit a button to trigger VBA running, but it seems that users had to click "OK" button inside the message box in oder to run VBA.  Is there anyway that VBA can run and the message can stay until done?  Thanks!

MsgBox ("please wait a moment, this process can take up to 15 seconds")
0
Comment
Question by:jjxia2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 50 total points
ID: 39755170
This is from http://www.tek-tips.com/faqs.cfm?fid=4699:
[...]Message boxes are always modal, which means that the underlying application (e.g. Excel) waits for a response from the User[...]

It's well described how to get this stuff working ;-)

[...]One option is to use the Message Box facility directly, via the Windows API, instead of via the VBA interface.[...]
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 50 total points
ID: 39755173
or you may want to use the status bar instead, like
Application.StatusBar = "Please be patient..."
Application.StatusBar = iDone & " of " & iTotal & " items done."

Open in new window

0
 

Author Comment

by:jjxia2001
ID: 39755204
[...]One option is to use the Message Box facility directly, via the Windows API, instead of via the VBA interface.[...]

How to do it?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 13

Accepted Solution

by:
Jeff Darling earned 450 total points
ID: 39755210
Here is a sample that does what you want using a form as a dialog
WaitDialogExample.xlsm
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39755253
[...]One option is to use the Message Box facility directly, via the Windows API, instead of via the VBA interface.[...]

How to do it?

Great :-(
The link, I PROVIDED (http://www.tek-tips.com/faqs.cfm?fid=4699) exactly describes HOW TO DO IT ;-) Did you even open that link?!?
I am truly not after the point, BUT this is defintely not how EE should work!

Kind regards,
Alex
0
 

Author Comment

by:jjxia2001
ID: 39755277
Alex,

Yes, I opened the link, but I had to spend time to figure out.  But when I coped and pasted the other sample, it worked immediately.  Anyway I really appreciate your helps!
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39755286
I don't want to be too offensive now and I surely do not want to bother you, but do you really think, you're going to learn anything by just "copy & paste"?!

Anyways... I'm quite sure, you know what you're doing ;-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question