MsgBox or form for display only

You have a few options, load a Shape and work with its text, use the Assistant and add your own messages, build a Modaless UserForm and work with its Labels and TextBoxes or use a timed to close Message Box.

Here is a Cloud Shape that I use and like:

Sub PleaseWait()
'Standard Module code, like: Module1!

With ActiveSheet.Shapes.AddShape(msoShapeCloudCallout, 200, 150, 150, 100)
.Name = "Wait a bit"
.TextFrame.Characters.Text = "Please Wait..." & Chr(10) & "I am working..." & Chr(10) & Chr(10) & "NOW!"
.TextFrame.HorizontalAlignment = xlHAlignCenter
.TextFrame.VerticalAlignment = xlVAlignCenter
End With

'Here is a timer call, but you can add your code here and when done,
'use the ActiveSheet.Shapes("Wait a bit").Delete code to remove it or
'change the text with the Text code above sample again.
'Or, just re-build the Cloud again!
Application.OnTime Now + 10 / 86400, "WaitOver"
End Sub

Sub WaitOver()
'Standard Module code, like: Module1!

On Error GoTo myEnd
ActiveSheet.Shapes("Wait a bit").Delete

myEnd:
End Sub

Hi,
Thanks for the examples and I can see what the code does but I dont know how to add this to an existing macro.

I've got a macro Sub Line () that starts as shown below (there's plenty more of it !), how do I add your code into my code, sorry, but I'm definitely not an expert !

thanks for your help
Steve


Sub Lines()


Application.ScreenUpdating = False
'The following csv files need to be saved to the directory for the macro to work - Serial_Header, Serial_Lines and Serial_Payments

UserForm1.Show vbModeless

On Error GoTo errorhandler
'set up errorhandler label at bottom of macro, with messagebox

Sheets("Output_Header").Visible = True
Sheets("Output_Lines").Visible = True
Sheets("Output_Lines2").Visible = True
Sheets("Output_Payments").Visible = True

Sheets("Output_Lines").Select
Cells.Select
Selection.Copy
Sheets("Output_Lines2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select

'Code adds duplicate last record for each serial number

Dim Rng As Range

You stor the two Subs in a Standard Module, like Module1. Then in the sub you want the message to show when it runs, you Call the Sub "PleaseWait" before the slow code and when that code is finished you Call "WaitOver" like:

Sub xxx()
'some code here!

Call PleaseWait

'The slow code here!
'Slow code finished here!

Call WaitOver

End Sub

Hi Joe,
Got it to work.....nice cloud !

Thanks again for your help, most appreciated.

cheers
Steve

Hi there,

Not an answer to your question, but your code will run a lot quicker by removing all that unecessary selecting. So
would become

Nice one, thanks for the revised code.

As you can see, my vba knowledge is very bit limited, I tend to record the code then bolt on a few other bits so I appreciate your help in improving the effeciency.

Cheers
Steve