Removing selection and maintaining formatting
 

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

Removing selection and maintaining formatting
 

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

Removing selection and maintaining formatting
 

Thanks Nick
That worked fine
I presume you should

Application.CutCopyMode=TRUE

After the event?
Ian


"Nick Hodge" wrote in message
...
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
DTHIS
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" wrote in message
...
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

Removing selection and maintaining formatting
 

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
DTHIS
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" wrote in message
...
Thanks Nick
That worked fine
I presume you should

Application.CutCopyMode=TRUE

After the event?
Ian


"Nick Hodge" wrote in message
...
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
DTHIS
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" wrote in message
...
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