I have created a simple userform to add data to an Excel 2007 worksheet; the form contains two buttons,
one to add new rows of data and the other to close the form. The worksheet has been populated with data by users using the
userform and now I'd like to add functionality to allow the users to search for data, and to edit data that was found during
their search, in addition to adding new rows. My code appears below - I've removed many of the fields that show up on the
userform to simplify the code (there are actually 74 columns). Thanks in advance for any assistance.
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ContractData")
'find first empty row in database
ws.Cells(Rows.Count, 1) _
'ensure user has entered a Requested Document Type
Trim(Me.cboDocTypeReq.Value) = "" Then
MsgBox "Please enter a Requested Document Type"
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cboDocTypeReq.Value
ws.Cells(iRow, 2).Value = Me.txtFinDocNum.Value
ws.Cells(iRow, 3).Value = Me.cboInstitution.Value
ws.Cells(iRow, 4).Value = Me.txtRefDocNum.Value
ws.Cells(iRow, 5).Value = Me.txtProjStDate.Value
ws.Cells(iRow, 6).Value = Me.txtProjDur.Value
ws.Cells(iRow, 7).Value = Me.txtPRNum.Value
ws.Cells(iRow, 8).Value = Me.cboRequester.Value
'clears the data from the form
Me.txtFinDocNum.Value = ""
Me.cboInstitution.Value = ""
Me.txtRefDocNum.Value = ""
Me.txtProjStDate.Value = ""
Me.txtProjDur.Value = ""
Me.txtPRNum.Value = ""
Me.cboRequester.Value = ""
Private Sub cmdClose_Click()
'force use of button instead of using "X" to close form
UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"