Solved

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

Posted on 2011-03-04
13
1,167 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
ID: 35041482
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
ID: 35041603
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
ID: 35041629
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
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.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35041634
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
ID: 35041635
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
ID: 35041648
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35041649
>>>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
ID: 35041657
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
ID: 35041658
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
ID: 35041663
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
ID: 35041683
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
ID: 35041689
>>>(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
ID: 35061231
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 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