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

SKV

Active Member
Joined
Jan 7, 2009
Messages
257
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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;
Code:
Sub display_message()
'Macros go here
MsgBox ("Macro finished")
End Sub

HeyYouYesYouNoNotYouYou
 
Upvote 0
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:
Code:
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
 
Upvote 0
Upvote 0
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:
Code:
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:

Code:
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:

Code:
bDlgOpen = IsUserFormLoaded("PlsWaitFrm")

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

The code for the IsUserFormLoaded function is below:

Code:
' 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:
Code:
Unload PlsWaitFrm

Apologies if this was a long winded solution to a simple question!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,261
Messages
6,171,075
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top