Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a Row
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 I built a small form that uses the following code to bring up userform for editing the row. Private Sub UserForm_Initialize() Dim i& With ThisWorkbook.Sheets("Active Collection") For i& = 3 To 120 i& = LTrim(RTrim(i&)) cmbClient.AddItem .Cells(i&, 7).Value Next i& End With cmbClient.ListIndex = 0 End Sub It opens the following form. Private Sub UserForm_Initialize() Dim i As Long i = frmLookUp.cmbClient.ListIndex txtCompany.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 4).Value)) txtName.Text = LTrim(RTrim(Sheets("Active Collection").Cells( + 3, 5).Value)) txtPhone.Text = LTrim(RTrim(Sheets("Active Collection").Cells( + 3, 6).Value)) txtInvoiceNo.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 7).Value)) 'txtInvoiceType.Text = Sheets("Active Collection").Cells(i + 3 8).Value txtInvoiceDate.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 9).Value)) txtAmount.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 10).Value)) txtSubStartDate.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 11).Value)) txtWhichInvoice.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 12).Value)) txtPaid.Text = LTrim(RTrim(Sheets("Active Collection").Cells( + 3, 13).Value)) txtComments.Text = LTrim(RTrim(Sheets("Activ Collection").Cells(i + 3, 22).Value)) txtCompany.SetFocus End Sub What I want to do is edit the information useing the userform and hav it edit the excel spreadsheet entry. Any suggestions on where t start?????? By the way, if some of you see a winking thing ingor it....thanks for any help to get me going in the right direction -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a Row
Would this best be done when I slect the row have it delete it once i
has filled in the userform, then when I click update it inserts it as new line -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing | Excel Discussion (Misc queries) | |||
pop up box needs editing --- HOW? | Excel Discussion (Misc queries) | |||
Min Max Editing | Excel Worksheet Functions | |||
Min Max Editing | Excel Discussion (Misc queries) | |||
Editing the name box | New Users to Excel |