"Waiting Message"

  • Thread starter Thread starter Guinness
  • Start date Start date
G

Guinness

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.
 
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
 
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.
Click to expand...

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.
 
Back
Top