DISPLAYIN A MESSAGE TO THE USER

cmazur71

Board Regular
Joined
Aug 7, 2003
Messages
63
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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.
 
Upvote 0

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
 
Upvote 0

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

Forum statistics

Threads
1,186,363
Messages
5,957,425
Members
438,305
Latest member
farmerje

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