Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert row after data from user form
I have the following code for a user form and would like to have it also add
a row after the information has been posted in the database (that way other information that's located below the database keeps getting pushed down when items are added and will always provide a spot for the next entry) Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Loan Summary and Comments") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(10, 0).Row 'check for Loan type If Trim(Me.txtloantype.Value) = "" Then Me.txtloantype.SetFocus MsgBox "Please enter Loan Type" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 2).Value = Me.txtloantype.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value ws.Cells(iRow, 4).Value = Me.txtTerm.Value ws.Cells(iRow, 5).Value = Me.txtAmortization.Value ws.Cells(iRow, 6).Value = Me.txtRate.Value 'clear the data Me.txtloantype.Value = "" Me.txtAmount.Value = "" Me.txtTerm.Value = "" Me.txtAmortization.Value = "" Me.txtRate.Value = "" Me.txtloantype.SetFocus End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert row after data from user form
You could put this in after the code segment where you add data to the
database. Application.CutCopyMode = False 'prevent entering clipboard data ws/Rows(iRow +1).Insert But I don't know why you would want to do that since you already have found the last row with data and moved down 10 rows from there to make your last entry. You are going to end up with a lot of blank rows.. "JeffK" wrote in message ... I have the following code for a user form and would like to have it also add a row after the information has been posted in the database (that way other information that's located below the database keeps getting pushed down when items are added and will always provide a spot for the next entry) Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Loan Summary and Comments") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(10, 0).Row 'check for Loan type If Trim(Me.txtloantype.Value) = "" Then Me.txtloantype.SetFocus MsgBox "Please enter Loan Type" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 2).Value = Me.txtloantype.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value ws.Cells(iRow, 4).Value = Me.txtTerm.Value ws.Cells(iRow, 5).Value = Me.txtAmortization.Value ws.Cells(iRow, 6).Value = Me.txtRate.Value 'clear the data Me.txtloantype.Value = "" Me.txtAmount.Value = "" Me.txtTerm.Value = "" Me.txtAmortization.Value = "" Me.txtRate.Value = "" Me.txtloantype.SetFocus End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
Insert chart into user form | Excel Programming | |||
using a user form to add data | Excel Programming | |||
User Form texbox insert text in cell - excel 2003 | Excel Programming | |||
User form to sort data | Excel Programming |