Displaying a message while a macro is running

K

kevin

hi

I have a macro that takes a few seconds to run. is there a way to have a msg box appear when the button to run the macro is clicked with a message like "processing" for example and then disapear when the macro is finished running ie there are no buttons that the user can play wit

Many thank
kevin
 
J

Jean-Yves

Hi ,

An easy way is to use the statusbar (well explained in help - see
displaystatusbar as well).
Another way
1.start a sub (start sub ..?) that call a form
2.from the form Activate event call the main sub
3. In the main sub, change the value on the form you want and use
form.repaint to update it.

Regards,

Jean-Yves

kevin said:
hi,

I have a macro that takes a few seconds to run. is there a way to have a
Click to expand...
msg box appear when the button to run the macro is clicked with a message
like "processing" for example and then disapear when the macro is finished
running ie there are no buttons that the user can play with
 
M

Melanie Breden

Hi Kevin,
I have a macro that takes a few seconds to run. is there a way to have a msg box appear when the button to run the macro is
clicked with a message like "processing" for example and then disapear when the macro is finished running ie there are no
buttons that the user can play with
Click to expand...

another possibility:
Provide from the Control-Toolbox a label on your sheet
and mark and format it over the Properties-window.
At the beginning of your procedure set the Visible-property on True
and at the end again on False.

Sub ShowLabel()
Worksheets(1).Label1.Visible = True
' your Code

' test
Application.Wait (Now + TimeValue("0:00:10"))
Worksheets(1).Label1.Visible = False
End Sub

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
M

Mike Fogleman

I devised a simple way to let them know the macro was working. Assuming the
macro is going to change some value or values in a cell on the spreadsheet,
I would make that cell visible on the sheet (if it wasn't already), put the
word PROCESSING in the cell, let the macro do its thing and then overwrite
PROCESSING with the results of the macro.

Sub
Sheets("Analysis").Activate
Application.ScreenUpdating = True
Range("L42").Select
Range("L42").Value = "Calculating Solution Codes"
Application.ScreenUpdating = False

'Your macro runs here

Range("L42").Value = Your macro results
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End Sub

Mike F

kevin said:
hi,

I have a macro that takes a few seconds to run. is there a way to have a
Click to expand...
msg box appear when the button to run the macro is clicked with a message
like "processing" for example and then disapear when the macro is finished
running ie there are no buttons that the user can play with
 
Top