Last Question for the day I promise.


I have a small VBA app that has Text Boxes and Option Buttons. The
insert into a spreadsheet using the following code.


Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Active Collection").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

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("A1").Select
Call frmNewCollect_Initialize
End Sub

__________________________________________________ _
My question is...Is there a way to have rows with formulas at th
bottom of this spreadsheet and each time the user adds a row from th
user form have it insert between the previous entry and the formul
rows at the bottom. In plain English, have the form be dynamic enoug
so that I don't have to have a fixed number of coulmns between firs
entered and formula row??

--
Message posted from http://www.ExcelForum.com