Outlook VBA Macro: Best way to indicate 'please wait'

Question

What's the best practice for indicating to the user that a Macro is running within Outlook ? The macro can take around 1-30 seconds to complete.

I want to avoid a modal 'msgbox' popping up before the macro is run, as this can be annoying.

I would rather avoid the hourglass cursor if possible, and wondered if there was a better way.

Is there a way of placing a non-modal 'status' message up, whilst the macro is running?

(The macro I have runs against the currently selected mailItem - and it launched by a button on the Quick Access Toolbar).

Answer 1

This article (also this) on best practice says use the status bar.

This article on Outlook says:

Changing the Status Bar
There is no way to change the status bar text in Microsoft Outlook. The status bar is not exposed as it is in other Microsoft Office object models.

Outlook.com provides code for a progress box.

Answer 2

Couple of things that string to mind, I am sure other will have ideas as well.

1.Show a form with a progress bar on it that reports progress or has the progress bar in marque mode if you can’t report progress 2.Show a form with a picture box with your favourite animated gif inside(spinny pizza etc.). You can turn off the buttons etc. 3. Use win api to get play with the outlook staus bar

Not knowing what you are doing in your macro you may have to deal with keeping the form “On top” and pumping async progress into it.

Cheers

Marcus

Answer 3

Expanding on @76mel's answer, a nice way to do this is with a non-modal userform. Make something really simple with just a label and caption like this:

What I like to do is have the userform set as:

• Non modal (in properties F4, set ShowModal to false)
  • This means you can click outside the status bar and it doesn't stop you.
• I set the StartupPosition to 0-Manual and Top and Left to something like 100 so that the Status form appears in the top left corner of the screen (out of the way of any other messages which appear in centre by default)

Set the label's value to some default text for when the Userform first loads

Public strStatus As String
Public Const defaultStatus As String = "Default status text" 'set this to whatever you want

Sub statusReporter()
frmStatus.Show
'''
'Your code here
'''
    frmStatus.lblStatus = "Step 1"
    '...
    frmStatus.lblStatus = "Step 2"
    '...
'''
'Unload the form
'''
frmStatus.lblStatus = defaultStatus
frmStatus.Hide
End Sub

Note, like with Excel's Application.Statusbar you must reset the userform to its default value if you plan to use it later on in the same instance of Excel Optionally use this too

'Written By RobDog888 - VB/Office Guru™
'Add a Command Button so you can toggle the userform's topmost effect

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
                    ByVal lpClassName As String, _
                    ByVal lpWindowName As String) As Long

Private Declare Function SetWindowPos Lib "user32" ( _
                    ByVal hwnd As Long, _
                    ByVal hWndInsertAfter As Long, _
                    ByVal X As Long, _
                    ByVal Y As Long, _
                    ByVal cx As Long, _
                    ByVal cy As Long, _
                    ByVal wFlags As Long) As Long

Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private mlHwnd As Long


Private Sub UserForm_Initialize()
Dim overTim As Single
overTim = Timer
    mlHwnd = FindWindow("ThunderDFrame", "Status") 'Change "Status" to match your userforms caption
    Do While mlHwnd = 0 And Timer - overTim < 5
        mlHwnd = FindWindow("ThunderDFrame", "Status")
        DoEvents
    Loop
    'Set topmost
    SetWindowPos mlHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
End Sub

in the userform code itself to keep it on top always