[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-03-04
13
Medium Priority
?
1,361 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
[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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

650 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