DISPLAYIN A MESSAGE TO THE USER

cmazur71

Board Regular
Joined
Aug 7, 2003
Messages
61
I have a VBA routine that takes about 30 seconds to complete (I open a workbook on our server, make a change to the workbook, and then save & close the workbook)
This is done automatically without the user knowing it's happening.

I would like to display a message to the user, stating that the file is being updated, and to please wait. I have used MsbBox, but I don't want the user to have to press the OK button.

Is there another way to display a message telling the user to please wait? The users think that excel has locked up, when it hasn't.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Excellent1

New Member
Joined
Oct 14, 2002
Messages
41
I recommend you link the routine to the msgbox vbyes or something like that. prompt them in the message box of how long it may take. I think there might be an activeX control that you could use to show the progress of a routine, but I've never used it.
 

earlyd

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
Here's a custom way to do it... Create a sheet called WAIT, which is blank except for a message that you want to display while waiting. Then when you want to run your routine, run the following macro (just change the value in the delay variable from 5 to whatever time delay works for you) NOTE this will only work if delay is less than a minute:

Sub timeDelay()
Dim XSec As Integer, delay As Integer
Dim delayReached As Boolean, x As Double, y As Double
Dim YSec As Integer, Holdname As String
Holdname = ActiveSheet.Name
Sheets("WAIT").Select
delay = 5
x = Time
XSec = Second(x)
XSec = XSec + delay
If XSec > 60 Then
XSec = XSec - 60
End If
delayReached = False
Do Until delayReached
y = Time
YSec = Second(y)
If XSec = YSec Then
delayReached = True
End If

Loop
Sheets(Holdname).Select

MsgBox "Finished processing file"

End Sub
 

earlyd

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
Oh - since you are opening another file, probably want to add application.screenupdating = false
:biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,876
Messages
5,766,875
Members
425,383
Latest member
IllDo

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
Top