Displaying a message while a macro is running
 

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

Displaying a message while a macro is running
 

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" wrote in message
...
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 with

Many thanks
kevin

Displaying a message while a macro is running
 

Hi Kevin,

kevin wrote:
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

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)

Displaying a message while a macro is running
 

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" wrote in message
...
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 with

Many thanks
kevin