MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to show temp Msg?

  Asked By: Diem    Date: Dec 27    Category: MS Office    Views: 2437

*Is there a way to show temporary information in a VBA routine?
I have a HUGE sheet and there are noticeable delays when sorting columns.
Can I pop up a box to say "Please Wait - SORTING Now" until the sort is
done, then have it disappear?
MsgBox requires a response, so not suitable.
Any ideas ?



10 Answers Found

Answer #1    Answered By: Abelard Fischer     Answered On: Dec 27

Sticking his nose in where it probably doesn't belong, Steve,

How about a form. I suspect they are under program control and can
be hidden via command rather than button....been a while since I did
my forms.

Answer #2    Answered By: Birke Fischer     Answered On: Dec 27

The November 1997 edition of Visual Basic Programmer's Journal had an article
about creating a self-clearing messagebox. I made a clumsy attempt to implement
it in Excel VBA. I created a tiny userform (frmNuMsg1) that contained only a
single command button (cmdMsg), which had its AutoSize property set to True. The
Click event for cmdMsg would unload the form, but an OnTime function call could
also unload it. In a code module I put the following two subroutines:

Sub ShowNuMsg(InMsg As String, Optional InTitle = "Message", Optional HowLong
= 0)
Dim Secondz As String
'If InMsg param is NULL (""), exit sub.
If InMsg$ = "" Then Exit Sub
'If duration param is 0 (or omitted), compute a default duration based on
'length. Allows about 1.5 seconds per line
If HowLong = 0 Then
HowLong = CInt(Len(InMsg$) / 45) + 2
End If
Secondz$ = "00:00:" & Trim(Str$(HowLong))
'Start the timer (OnTime function) to remove the message form automatically, if
'the user doesn't remove it first.
Application.OnTime Now + TimeValue(Secondz$), "KillNuMsg"
'Reposition & resize the userform to just fit the command button.
frmNuMsg1.Caption = InTitle
frmNuMsg1.cmdMsg.Caption = InMsg$
frmNuMsg1.cmdMsg.Top = frmNuMsg1.Top
frmNuMsg1.cmdMsg.Left = frmNuMsg1.Left
frmNuMsg1.Height = frmNuMsg1.cmdMsg.Height + 18
'84 seems to be the minimum width for a userform
If frmNuMsg1.cmdMsg.Width < 84 Then
frmNuMsg1.cmdMsg.Width = 80
End If
frmNuMsg1.Width = frmNuMsg1.cmdMsg.Width + 4
'Display the message form.
End Sub

Private Sub KillNuMsg()
If frmNuMsg1.Visible = True Then
Unload frmNuMsg1
End If
End Sub

You can tell it how long to remain displayed, or let it come up with a duration
based on the message length.
Call ShowNuMsg("This is a test", "Test #1", 9) 'Displays for 9 seconds
Call ShowNuMsg("This is a test", "Test #1") 'Displays for about 2 seconds

Answer #3    Answered By: Thomas Thompson     Answered On: Dec 27

A problem is that execution time differs dramatically depending on the
machine running it.
For example, my 3.0 Gig processor takes about 9 seconds, while I can
only guess what a slow machine would take.
Ideally, a msg  with VBA command termination is needed (maybe an API
call, like the Windows hourglass cursor).

Answer #4    Answered By: Morris Hughes     Answered On: Dec 27

So you just want to display a form with a message, and unload it when the sort
is done. The problem is to keep Excel from executing the Unload command before
the sorting  is finished. I had a similar problem once, and I created a Boolean
variable (DoneFlag) which I set to False initially. Then I began a loop in which
I executed my code (in my case, Hyperion Essbase retrieves). The loop continued
as long as DoneFlag was False. To determine when DoneFlag should be set to True,
the loop examined the contents of a particular cell. When the Essbase retrieve
was done, that cell would be overwritten with a different value. The loop
included a DoEvents command also.

Perhaps you could do something similar by noting the values in several cells
before you begin sorting, and give the Unload command when you see that any has
changed. This method can work, but depending on your data there may a risk that
the cells would end up with the same values after sorting as they had before
sorting (causing an infinite loop).

Answer #5    Answered By: Leo Evans     Answered On: Dec 27

Pearson Software Consulting provides a free product
that I've used in the past. They describe it as follows

The best way to display diagnostic messages to users and
developers. Overcomes the limitations of MsgBox, Debug.Print,
and Application.Statusbar.
Recommended for intermediate and advanced developers.

It can be downloaded from

Answer #6    Answered By: Gina Tanaka     Answered On: Dec 27

You could display a userform containing a textbox or label. Use the form's .Show
method to display the form, then either use the .Hide method or the Unload
statement to remove the form.

Answer #7    Answered By: Mehr Malik     Answered On: Dec 27


Shows how to create and use a progress bar in Access. You may be able
to use the same processes in Excel.

Answer #8    Answered By: Nathaniel Martin     Answered On: Dec 27

You can add this command before running the sorting  column routine in your code

Application.StatusBar = "Sorting columns...Please wait."

Add this after your sorting column routine in your code

Application.StatusBar = False

Answer #9    Answered By: Rachel Fischer     Answered On: Dec 27

I use the status bar for this type of display.

Answer #10    Answered By: Shannon Johnson     Answered On: Dec 27

Not adding much to the discussion but, by way of an example, here's some
code. First create a user form called frmMessage and add a label to it
called lblMessage

In frmMessage's Initialize routine put Me.Hide so it doesn't display at

Then you're ready to enter the following code in your worksheet.

Private Sub messageTest()

Load frmMessage

For r = 1 To 20


pause 'Wait a second to slow down execution to human readable

Cells(r, 1) = r

If r > 4 Then
frmMessage.lblMessage.Caption = "Stuff is happening..." &
vbCrLf & "Please Wait"
frmMessage.Show False
End If

If r > 15 Then frmMessage.lblMessage.Caption = "Nearly done"


unload frmMessage

End Sub

Sub pause()

Dim startTimer As Single

startTime = Timer

While Timer - startTime < 1
End Sub

Didn't find what you were looking for? Find more on How to show temp Msg? Or get search suggestion and latest updates.