"Waiting Message"
I was wondering how to activate a waiting message to pop up while one of my
longer macros is running, so that the users do not think the program froze. I was given one VBA code, but I couldnt get it to work. Thank you. |
"Waiting Message"
Andy Pope has a variety of progress meters he
http://andypope.info/vba/pmeter.htm which might look a bit better than just "wait". There is code that you can incorporate into your own routines. You can also write to the status bar. Hope this helps. On Nov 27, 10:11 pm, Guinness wrote: I was wondering how to activate a waiting message to pop up while one of my longer macros is running, so that the users do not think the program froze. I was given one VBA code, but I couldnt get it to work. Thank you. |
"Waiting Message"
Hi Guinness,
The following is not a progress message but I have found it to be OK to advise the user that something is occurring. The progress bars etc that I have attempted to use slow the entire process too much for my liking. Sub Process_Msge() Dim oleObjText As OLEObject With ActiveSheet Set oleObjText = .OLEObjects.Add(ClassType:="Forms.TextBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=143.25, Top:=25.5, _ Width:=192.75, _ Height:=51) End With With oleObjText .Object.MultiLine = True .Object.Text = "Please be patient" & Chr(10) & _ "Your data is being processed" & Chr(10) & _ "This message will close on completion" End With oleObjText.Activate 'Insert the processing code here 'in lieu of the msgbox MsgBox "Text box created with message" oleObjText.Delete End Sub -- Regards, OssieMac "Guinness" wrote: I was wondering how to activate a waiting message to pop up while one of my longer macros is running, so that the users do not think the program froze. I was given one VBA code, but I couldnt get it to work. Thank you. |
"Waiting Message"
On Nov 27, 5:11 pm, Guinness
wrote: I was wondering how to activate a waiting message to pop up while one of my longer macros is running, so that the users do not think the program froze. I was given one VBA code, but I couldnt get it to work. Thank you. If your macro runs long, one solution could be to simply update the status bar every so often. For instance, let's say I have a For loop that populates some cells, etc. In this case, I have DoEvents, just for sake of making this seem slow: Public Sub statusBarUpdate() Dim i As Long Const lastItem As Long = 1000000 For i = 1 To lastItem ' whatever you're doing DoEvents ' update status bar every 1 percent If (i Mod (lastItem \ 100)) = 0 Then Application.StatusBar = "Processing " & _ Int(100 * i / lastItem) & "% complete" End If Next i ' reset status bar Application.StatusBar = False End Sub If you have multiple loops in your macro, consider giving them different names. Connecting, Waiting, Processing, Analyzing, Compiling, Preparing Report - just to keep users a little engaged - or even display something more meaningful than just the % completion (name of item being processed, file name like installer programs do, or whatever your scenario is). The trick is to do the update infrequently, so as not to slow down the operation by much. |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com