Solved

Why is my WshShell popup acting modal (instead of timing out as expected)?

Posted on 2011-03-04
13
1,119 Views
Last Modified: 2012-05-11
I want to inform the excel user that a vba sub (running from personal.xls) has finished via a message box, but I'd prefer to use the popup method - it just seems cleaner to me to have something that times out after a few seconds and lets the sub wrap up, versus the modal msgbox method that brings everything grinding to a halt.

(For example, I use the Autosafe plugin - freaking awesome plugin - but it cannot execute while a modal msgbox is up. This means that if I were to running a simple script as I leave the office for the night, the msgbox patiently waiting for my "OK" could deny me Autosafe's mighty lifesaving abilities.)

So I use the following code:

Sub GetAlldata()
On Error GoTo GetAlldata_Error


Dim pop As IWshRuntimeLibrary.WshShell
Dim popRsp As Integer 'Popup response - added this because I thought maybe it was a neccessary evil

'Buncha stuff happens
'Buncha stuff happens
'Buncha stuff happens
'Buncha stuff happens
'Buncha stuff happens

Set pop = CreateObject("WScript.Shell")
popRsp = pop.Popup("Finished importing data", 3, "Done!", 0) ' '3' is 3 seconds, 0 is a popup window with just "ok"
DoEvents ' doesn't seem to affect anything
Set pop = Nothing

MsgBox ("whee") ' I added this to see if the pop.Popup was acting modal
				' sho 'nuff, it doesn't pop up until I hit OK

Exit Sub

GetAlldata_Error:
Debug.Print Err.Number; Err.Description

End Sub

Open in new window


Unfortunately, the popup method seems to act exactly like the msgbox method in "application modal" mode - everything stops until I press OK. No code after the pop.Popup executes until that OK; (note the msgbox test in the code). Not after 3 seconds, not after 300.

What gives?
0
Comment
Question by:Dsastray
  • 7
  • 6
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
This works for me.

Set pop = CreateObject("WScript.Shell")
pop.Popup "Finished importing data", 3, "Done!"
Set pop = nothing

Sid
0
 

Author Comment

by:Dsastray
Comment Utility
Sub poppit()

Dim pop As IWshRuntimeLibrary.WshShell

Set pop = CreateObject("WScript.Shell")
pop.Popup "Finished", 3
Set pop = Nothing

End Sub

Open in new window


doesn't work for me...

I'm going to see if there is an updated version of wshom.ocx  (mine is version 5.7.0.18066).
0
 

Author Comment

by:Dsastray
Comment Utility
Okay, update: that DOES work for me, if it is in a module in an access database. It works incorrectly (as I have documented above when the sub is in a module in excel (where I want to use it, unfortunately).

The references in both the Access and Excel both point to the same file

c:\windows\system32\wshom.ocx

It is (is it?) interesting to note that when I paste the code into access, it corrects "Popup"  to "PopUp", but I think this may be an artifact of there being a "PopUp" property in the access model.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
It works for me in Excel module as well :)

Just remove the first line

Dim pop As IWshRuntimeLibrary.WshShell

Try this code

Sub poppit()
    Set pop = CreateObject("WScript.Shell")
    pop.Popup "Finished", 3
    Set pop = Nothing
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Or use this

Sub poppit()
    Dim pop
    Set pop = CreateObject("WScript.Shell")
    pop.Popup "Finished", 3
    Set pop = Nothing
End Sub

Open in new window


Sid
0
 

Author Comment

by:Dsastray
Comment Utility
No solution, but I think I may not be completely nuts:

http://www.pcreview.co.uk/forums/auto-close-pop-up-msgbox-t3795906.html


I am using Excel 2002, 10.6866.6867 SP3
Windows XP Pro SP3
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>I am using Excel 2002

I am not sure whether it works in Excel 2002 or not. I just tested this in Excel 2007

Sid
0
 

Author Comment

by:Dsastray
Comment Utility
Ugh. Is it finally time for me to learn to create and program my own userforms?

From http://www.pcreview.co.uk/forums/close-msgbox-automatically-t3705197.html

The WScript.Shell - popup method is not reliable to dismiss a msgbox. It may
work in some machines, not at all, or erratically (does not seem to relate
to windows version).

Best way is make a userform to look like a msgbox, perhaps pass a message to
a label caption and include some sort of timer to dismiss the form if still
showing after say 5 seconds
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Yes you can do that. Do you want help with that? I might have a code ready for that.

Sid

0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Just created this. See if this help?

Dim NextTime As Date

Sub poppit()
    NextTime = Now + TimeValue("00:00:03")
    Application.OnTime NextTime, "UnloadPOPUP"
    
    UserForm1.Show
End Sub

Sub UnloadPOPUP()
    Unload UserForm1
End Sub

Open in new window


Sid
0
 

Author Comment

by:Dsastray
Comment Utility
This looks very promising (btw, did try the version without the "dim".... no love).

It's about 9:30pm Friday where I am, and I am ready to call it quits, so I will try tomorrow or monday. Thanks for your persistence.

(As I will likely be going this route, I think I'll create another userform question for you, since you are going above and beyond. I'll alert you to it when I do.)
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
>>>(As I will likely be going this route, I think I'll create another userform question for you, since you are going above and beyond. I'll alert you to it when I do.)

No need to create another question as the Userform Code (tested and Tried) that I gave above is an alternative to your actual question :)

Sid
0
 

Author Closing Comment

by:Dsastray
Comment Utility
Works like a champ, and I'm well on my way to using forms. Don't know why I resisted so long.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now