Removing selection and maintaining formatting

[mantrid]

Hello
Im using the following code to insert a row at a particular point (that
changes), and copy the row above into it. It works OK but for two slight
problems
1) The newly added line remains selected (flashing dotted line around the
row) after the event. I have tried using deselect but I cant get it to work

With ActiveSheet
.Rows(RowNo - 1).Copy
.Rows(RowNo - 1).Insert (xlShiftDown)
.Paste Destination:=ActiveSheet.Rows(RowNo)
End With

2) Then after the previous code I use some more code as below

ActiveSheet.Cells(RowNo, 7).Value = Me.purchasedate
ActiveSheet.Cells(RowNo, 8).Value = Me.price
ActiveSheet.Cells(RowNo, 9).Value = Me.charges
ActiveSheet.Cells(RowNo, 20).Value = Me.price

To add numbers input on a form to populate the cells on the new row. Trouble
is the cells where the data is added loose their number formatting, and the
little green triangle in the top left of the cell appears giving the
message;
'The number in this cell is formatted as text or preceeded by an apostrophe'

Any idea anyone how I can resolve these problems?
Thank
Ian

 

[Nick Hodge]

Mantrid

1) Application.CutCopyMode=False will stop the 'marching ants'
2) Coerce the values e.g

ActiveSheet.Cells(RowNo, 7).Value = CDate(Me.purchasedate)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[mantrid]

Thanks Nick
That worked fine
I presume you should

Application.CutCopyMode=TRUE

After the event?
Ian

 

[Nick Hodge]

Mantrid

No it's a one way operation, no need to place Excel back in that mode.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk