Hey,
Can I make a message box with a comment saying "Thinking" and one of those sand turning time thingies to popup while a large macro is running??
i would look in help files and stuff, but frankly i dunno and keywords to look for.
Thnx for any help.
You cannot do that with a regular message box, but you can do that with a User Form.
Check out these Kb Entries.
MS Progress Bar
Dual Progress Bar
Status Bar
Animation on a Userform
Thanks alot man.
You're Welcome
Take Care
Just to add another variety to this, I had to make a progress bar for a vbscript I had (which can't use forms, etc). I decided to use an IE window for this, which works nicely for it. You can also use this variation in excel, as shown here:[vba]Option Explicit
Sub IEProgressExample()
Dim IE, vHead, vBar, i, progr, totl, IEOpen
'Initialize IE, set variables and initial heading
StartIE IE
Set vHead = IE.Document.All("vHead")
Set vBar = IE.Document.All("vBar")
IEOpen = True
'This is how you set the text to the different parts of the progress window
SetProgText vHead, "Working...", IEOpen
'example for loop to increment progress counter
totl = 2500
For i = 1 To totl
progr = CLng((i / (totl)) * 100)
SetProgText vBar, String(progr, "|") & String(100 - progr, "."), IEOpen
Next 'i
'clear variables
If IEOpen Then IE.Quit
Set IE = Nothing
Set vHead = Nothing
Set vBar = Nothing
Set IEOpen = Nothing
Set i = Nothing
Set progr = Nothing
Set totl = Nothing
End Sub
Function SetProgText(ByVal vDocumentObject, ByVal vObjectText, ByRef IEOpen)
If Not IEOpen Then Exit Function
On Error Resume Next
vDocumentObject.InnerHTML = vObjectText
If Err.Number <> 0 Then IEOpen = False
Set vObjectText = Nothing
Set vDocumentObject = Nothing
End Function
Function StartIE(ByRef IE)
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate2 "about:blank"
Do While .readyState <> 4
Loop
.Document.Title = "Progress"
.Document.Body.InnerHTML = _
"<BODY SCROLL='NO'><CENTER><FONT FACE='arial black' SIZE=2>" & vbLf & _
"<DIV id='vHead' ALIGN='Left'></DIV>" & vbLf & _
"<DIV id='vBar' ALIGN='Left'></DIV>" & vbLf & _
"</FONT></CENTER></BODY>"
.Document.Body.Scroll = "no"
.Toolbar = False
.StatusBar = False
.Resizable = False
.Width = 435
.Height = 100
.Left = 0
.Top = 0
.Visible = True
End With
End Function[/vba]Matt
Don't ask me how, but once I saw this I said "He's probably from Jersey"...then I scrolled down to find the New Jersey flagOriginally Posted by russkie
I guess we can just tell
Also, I think Matt's reply is awesomeI could TOTALLY use that!
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Hi Matt,
Very nice alternative, just one tiny tweak maybe... a "DoEvents" directly after the declaration of variables in "IEProgressExample" to allow time for the 'Macro' Dialog to unload just in case the user's actuating it from there (as I did).
Good Stuff
John
PS: So can we expect to see this submitted to the knowledge-base soon?
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Thats not a bad idea, that may actually take care of an issue I'm having with it in vbs.
Nope, just tested it, but I'll make sure to keep it in for the non-vbs ones (Fixed anyways in the update above)
Its on my list nowPS: So can we expect to see this submitted to the knowledge-base soon?One of these days I'll have some time to add these
This looks very interesting. Would it be possible to have the code place the IE window at a specific/ calculated location? Instead of top left corner of the screen centered?
Posting Permissions
Reply With Quote
