DISPLAYIN A MESSAGE TO THE USER

[cmazur71]

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.

 

[Excellent1]

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.

 

[Iridium]

You could try using the statusbar - an example here:

http://www.erlandsendata.no/english/vba/display/statusbarmsg.php

Check out the LED suggestion from that page also if a simple statusbar won't do

HTH

 

[earlyd]

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
If XSec = YSec Then
delayReached = True
End If

Loop
Sheets(Holdname).Select

MsgBox "Finished processing file"

End Sub

 

[earlyd]

Oh - since you are opening another file, probably want to add application.screenupdating = false

 

[Richie(UK)]

Hi Chris,

You may also be interested in Dave Hawley's approach here:

http://www.ozgrid.com/forum/viewthread.php?tid=752

HTH