I would just re-format it in the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"PW11111" wrote in message
...

One last thing Nick - its all working fine - except that when the text is
entered into the cell I specify - it loses all the formatting / font /
colour
etc..!

is there anyway around this..? Or maybe to specify the font etc in the
code.

Thanks Again.

"Nick Hodge" wrote:

Phil

You can use either VBA InputBox or Excel's The only difference is Excel's
is
prefixed with the Application Object and they accept slightly different
parameters (Read up in VBA help)

Using Excel's

prompt=Application.InputBox("Enter a Reason","Reason Entry","Default
Reason")
Range("A1").Value=prompt

Be careful to check for cancelled inputboxes, etc as well though

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"PW11111" wrote in message
...
Hi,

I have a spreadsheet that I am moving information (rows) from one tab
onto
another.

The spreadsheet logs projects - in either an arrivals or departures
tab.

I have written a macro to move these projects when the user clicks a
button.
(See code below).

Application.ScreenUpdating = False

varAnswer = MsgBox("Send to Completed?", vbOKCancel, "Please Confirm")
If ((varAnswer = 1)) Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Completed").Select
Rows("12:12").Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Departures").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Completed").Select
Else
End If
End Sub


I would like to modify it so when the user clicks the button the
confirmation is asked - and then prompts the user for a reason. The
user
can
then enter the reason in a text box - and this input can then be placed
in
a
cell of my choosing.

If anyone knows a simple way to do it - and could help it would be
great.

Thanks again

Phil