Solved

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

Posted on 2011-03-04
13
1,216 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
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…

740 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