Solved

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

Posted on 2011-03-04
13
1,234 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
Excel to show a dynamic Picklist at level2 2 23
can you help get  64bit version of these 3 APIs? 4 34
LOOK FOR 22 28
copy down array 24 32
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 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