Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Questions about inserting rows.
I use the following code to insert all of this information into a row.
Private Sub cmdOK_Click() ActiveWorkbook.Sheets("Active Collection").Activate Range("A65536").End(xlUp).Select If ActiveCell.HasFormula Then ActiveCell.EntireRow.Insert If ActiveCell.Row < 1 Then _ ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell.Offset(0, 1) = Date ActiveCell.Offset(0, 2) = Time ActiveCell.Offset(0, 3) = txtCompany.Value ActiveCell.Offset(0, 4) = txtName.Value ActiveCell.Offset(0, 5) = txtPhone.Value ActiveCell.Offset(0, 6) = txtInvoiceNo.Value ActiveCell.Offset(0, 7) = cmbInvoiceType.Value ActiveCell.Offset(0, 8) = txtInvoiceDate.Value ActiveCell.Offset(0, 9) = txtAmount.Value ActiveCell.Offset(0, 10) = txtSubStartDate.Value ActiveCell.Offset(0, 11) = txtWhichInvoice.Value ActiveCell.Offset(0, 12) = txtPaid.Value Select Case True Case opt30.Value ActiveCell.Offset(0, 13) = txtPaid.Value Case opt60.Value ActiveCell.Offset(0, 14) = txtPaid.Value Case opt90.Value ActiveCell.Offset(0, 15) = txtPaid.Value Case opt120.Value ActiveCell.Offset(0, 16) = txtPaid.Value Case opt121.Value ActiveCell.Offset(0, 17) = txtPaid.Value End Select ActiveCell.Offset(0, 18).Formula = "=SUM(RC14:RC18)" Select Case True Case optEOM.Value ActiveCell.Offset(0, 19) = txtNextAmount.Value Case optMOM.Value ActiveCell.Offset(0, 20) = txtNextAmount.Value End Select ActiveCell.Offset(0, 21) = txtComments.Value Range("A3").Select End Sub Question 1: How do I add formating so that the inserted value will b guarenteed to be the date, text, currency of my choosing and no whatever the celll was already formatted as? Question 2: I use a form to allow me to select an existing row, ho could I make the above code and UPDATE set of commands instead o inserting a new row -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Questions about inserting rows.
Q1., Set the NumberFormat property. For instance
Activecell.Numberformat = "dd mmm yyyy" Text format is "@" Q2. If I understand, just don't insert a row. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "stck2mlon " wrote in message ... I use the following code to insert all of this information into a row. Private Sub cmdOK_Click() ActiveWorkbook.Sheets("Active Collection").Activate Range("A65536").End(xlUp).Select If ActiveCell.HasFormula Then ActiveCell.EntireRow.Insert If ActiveCell.Row < 1 Then _ ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell.Offset(0, 1) = Date ActiveCell.Offset(0, 2) = Time ActiveCell.Offset(0, 3) = txtCompany.Value ActiveCell.Offset(0, 4) = txtName.Value ActiveCell.Offset(0, 5) = txtPhone.Value ActiveCell.Offset(0, 6) = txtInvoiceNo.Value ActiveCell.Offset(0, 7) = cmbInvoiceType.Value ActiveCell.Offset(0, 8) = txtInvoiceDate.Value ActiveCell.Offset(0, 9) = txtAmount.Value ActiveCell.Offset(0, 10) = txtSubStartDate.Value ActiveCell.Offset(0, 11) = txtWhichInvoice.Value ActiveCell.Offset(0, 12) = txtPaid.Value Select Case True Case opt30.Value ActiveCell.Offset(0, 13) = txtPaid.Value Case opt60.Value ActiveCell.Offset(0, 14) = txtPaid.Value Case opt90.Value ActiveCell.Offset(0, 15) = txtPaid.Value Case opt120.Value ActiveCell.Offset(0, 16) = txtPaid.Value Case opt121.Value ActiveCell.Offset(0, 17) = txtPaid.Value End Select ActiveCell.Offset(0, 18).Formula = "=SUM(RC14:RC18)" Select Case True Case optEOM.Value ActiveCell.Offset(0, 19) = txtNextAmount.Value Case optMOM.Value ActiveCell.Offset(0, 20) = txtNextAmount.Value End Select ActiveCell.Offset(0, 21) = txtComments.Value Range("A3").Select End Sub Question 1: How do I add formating so that the inserted value will be guarenteed to be the date, text, currency of my choosing and not whatever the celll was already formatted as? Question 2: I use a form to allow me to select an existing row, how could I make the above code and UPDATE set of commands instead of inserting a new row? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Questions about inserting rows.
So where would I put it on this one for a date?
ActiveCell.Offset(0, 8) = txtSubStartDate.Valu -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Questions about inserting rows.
With ActiveCell .Offset(0, 8) = txtSubStartDate.Value .NumberFormat = "dd mmm yyyy" End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "stck2mlon " wrote in message ... So where would I put it on this one for a date? ActiveCell.Offset(0, 8) = txtSubStartDate.Value --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting rows inbetween rows of data ? | Excel Discussion (Misc queries) | |||
Inserting a MS Word object into an Excel spreadsheet (2 questions) | Excel Worksheet Functions | |||
Questions about inserting an object into a worksheet | Excel Discussion (Misc queries) | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
2 questions - one about inserting the date, other about adding lines. | New Users to Excel |