Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining currency formatting in a Mail Merge? | Excel Discussion (Misc queries) | |||
Maintaining Formatting in a UserForm | Excel Programming | |||
Maintaining pivot table formatting | Excel Programming | |||
Maintaining formatting - Excel = Word | Excel Programming | |||
Maintaining Text Formatting in a Lookup | Excel Discussion (Misc queries) |