Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
can i get the "message pane" underneath the "threads pane" instea | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |