vba code to display message "In progress" while the code is running

[SKV]

Hi,

What is the best and most effective code to display a message "In Progress" or put hourglass while the code is running. Please share the code.

Thanks

 

[HeyYouYesYouNoNotYouYou]

Hi SKV,
If I were to display a message it would be to show the macro has finished, this is because most are done almost instantly, therefore I would use code something like;

Sub display_message()
'Macros go here
MsgBox ("Macro finished")
End Sub

HeyYouYesYouNoNotYouYou

 

[ZAX]

Hi,

What is the best and most effective code to display a message "In Progress" or put hourglass while the code is running. Please share the code.

Thanks

Hi SKV,
I can try to work it out for you but i wonder, why would you need that? why don't you just use the ScreenUpdating property like this:

Application.ScreenUpdating = False
'code goes here
Application.ScreenUpdating = True

This will finish the code without changing the current view i.e it will work in the background.
ZAX

 

[HiTechCoach]

Hi,

What is the best and most effective code to display a message "In Progress" or put hourglass while the code is running. Please share the code.

Thanks

In Access this is common. I like to use the Hourglass.

Example:

DoCmd.Hourglass True  ' turn on Hourglass

' do something here

DoCmd.Hourglass False ' turn off hourglass

Also see:

MS Access - Quick and Easy Progress Bar

and

Progress Bars and Microsoft Access

 

[Excelot]

Hi SKV. If you want to display a messge rather than an hour-glass, here is a method I use a lot in Excel that I'm sure would work in MS Access.

Create a user form (e.g. called 'PlsWaitFrm') and add a label. Make sure the 'ShowModal' property is equal to False.

Just before you need to use it, display the userform using:

PlsWaitFrm.Show vbModeless

This will ensure the userform is running modless (i.e. it doesn't stop execution of any other code, like a modal userform would).

You could just make the label display a message like 'Pleaes wait...', but if you are iterating through a list or number of records or something, you could use the label to update the number of records or the name of the current record for example. Say you wanted to show the name of the current record being updated/actioned, you can do the following:

PlsWaitFrm.Label1.Caption = "Updating record for " & strRecordName & "..."
DoEvents

Where strRecordName (just as an example) is a variable holding the persons name of that record. If the person was "John Citizen" and this is stored in the variable strRecordName, the userform will show, "Updating record for John Citizen...".

The DoEvents built-in function is used to pass control onto the operating system to ensure messages are processed. You may not strictly need this, but I found it forces changes to the userform to be shown (especially if you have Application.ScreenUpdating equal to False).

Lastly, if you're using the same userform from different functions/subs, then you should include a check to make sure that the userform is still shown (hasn't been accidentally closed down by a previous sub/function), something like:

bDlgOpen = IsUserFormLoaded("PlsWaitFrm")

If bDlgOpen Then
    PlsWaitFrm.Label1.Caption = "Your message here!!!"
    DoEvents
End If

The code for the IsUserFormLoaded function is below:

' Code taken from URL: [URL="http://www.ozgrid.com/forum/showthread.php?t=152892"]Test if a specific userform is loaded[/URL]
Public Function IsUserFormLoaded(ByVal UFName As String) As Boolean
    Dim UForm As Object
     
    IsUserFormLoaded = False
    For Each UForm In VBA.UserForms
        If UForm.Name = UFName Then
            IsUserFormLoaded = True
            Exit For
        End If
    Next
End Function

When you no longer need the userform any more use:

Unload PlsWaitFrm

Apologies if this was a long winded solution to a simple question!

 

[dlingerf1]

This is very similar to a problem I am having now. I figured out that I should use the "modeless" keyword to keep the code running, but when I do, the userform (with the label " Working, Please wait.") shows blank white box ony. I close it off at the end of my macro with an "unload userform2" command. When I do not include the "modeless" keyword, the form shows the label, but I have to close it manually (and it does not run the rest of the macro until I do). I even tried to load the form before showing it, but without effect. Does anyone know why this is happening?

 

[shg]

Maybe ...

yourFormName.Repaint

 

[dlingerf1]

Maybe ...

yourFormName.Repaint

Thank you. This worked perfectly.

 

[shg]

You're welcome.