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 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/

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

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/