Senji
Board Regular


Joined: 23 Mar 2002
Posts: 21


Status: Offline

   

Trying to be funny and want my little macro to pause for like 5 to 10 seconds with the message Processing.... in a msgbox and then to disappear after the timer is up without pressing a key, is that possible?

Thanks

Juan Pablo González
MrExcel MVP


Joined: 09 Feb 2002
Posts: 8536
Location: Marion, IL
Flag:

Status: Offline

   

Yes, possible... and asked quite a few times as well...

The options are from the very simple of having a textbox from the drawing toolbar displayed in the sheet, and hide it with the macro to the complicated ones (Coded by Ivan F Moala) that uses some API calls to dismiss the MsgBox automatically.

Just a matter of how 'fancy' you want to go...
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

kskinne
Board Master


Joined: 18 Feb 2002
Posts: 947

Flag:

Status: Offline

   

I got this off the board and it works well for me with the workbook_open event:

code:

---

CreateObject("WScript.Shell").Popup "Place message here", 3, "Message Title"

---

Senji
Board Regular


Joined: 23 Mar 2002
Posts: 21


Status: Offline

   

Basically, what I have written right now is something I threw together in 15 minutes... they open up a spreadsheet, there is a button that says Push Me for Quote or Purchase Order Number....

they push it
1) it asks for Company Name(for quote or po)
2) then their name
3) Quote or PO?

This is all done with InputBox presently. Then displays the next number available for quote/po...

I just want it to pause for 5 or 10 seconds.... in a box that says...

Processing.....


then the box can either change it wording and display the number to use
or just disappear and open the next msgbox in the macro...

any ideas where to get that type of code or understanding of which to use?

pennysaver
Board Master


Joined: 15 May 2003
Posts: 2137
Location: San Diego, California
Flag:

Status: Offline

   

You can build a User Form that displays your "Processing..." dialogue in the caption, in the code put:

Private Sub UserForm_Initialize()

lblMessage.Caption = Processing_Message

End Sub

Private Sub UserForm_Activate()

Me.Repaint
Application.Run Macro_to_Process
Unload Me

End Sub

Then Insert a module and put:

Public Processing_Message As String
Public Macro_to_Process As String

Sub StartProcessing(msg As String, code As String)

Processing_Message = msg

Macro_to_Process = code

Processing_Dialog.Show

End Sub

Sub MyMacro()

For x = 1 To 50000
Application.StatusBar = x

Next

Application.StatusBar = False

End Sub
Sub Main()
StartProcessing "I'm Working On It! Just Hang On!", "Update_Now"
End Sub

I got that one from Microsoft a few years ago when they still gave out help...