command button code on user form

Question

i want to add a button onto a user form i've created that will allow a user to update a record in my worksheet.  i currently have an add new site button on the form.  here is all of the code for my userform.  you might not need to see it all, but i thought it best to put everything in my reply.  can anyone help me with the code for a user form that just updates a record instead of adding a new one?  thanks!

Private Sub cmdAdd_Click()

Dim iRow As Long

Dim ws As Worksheet

Set ws = Worksheets("Active")

'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _

  .End(xlUp).Offset(1, 0).Row

'check for a part number

If Trim(Me.TxtSite.Value) = "" Then

  Me.TxtSite.SetFocus

  MsgBox "Please enter a site name"

  Exit Sub

End If

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.TxtSite.Value

ws.Cells(iRow, 2).Value = Me.TxtAddress.Value

ws.Cells(iRow, 3).Value = Me.TxtState.Value

ws.Cells(iRow, 4).Value = Me.TxtZip.Value

ws.Cells(iRow, 5).Value = Me.TxtID.Value

ws.Cells(iRow, 6).Value = Me.TxtAgency.Value

ws.Cells(iRow, 7).Value = Me.TxtFAD.Value

ws.Cells(iRow, 8).Value = Me.TxtDate.Value

ws.Cells(iRow, 9).Value = Me.TxtNPL.Value

ws.Cells(iRow, 10).Value = Me.TxtFF.Value

ws.Cells(iRow, 11).Value = Me.TxtInitiative.Value

ws.Cells(iRow, 12).Value = Me.TxtRPM.Value

ws.Cells(iRow, 13).Value = Me.TxtDocDate.Value

ws.Cells(iRow, 14).Value = Me.TxtPA.Value

ws.Cells(iRow, 15).Value = Me.TxtSI.Value

ws.Cells(iRow, 16).Value = Me.TxtFile.Value

ws.Cells(iRow, 17).Value = Me.TxtUpdate.Value

'clear the data

Me.TxtSite.Value = ""

Me.TxtAddress.Value = ""

Me.TxtState.Value = ""

Me.TxtZip.Value = ""

Me.TxtID.Value = ""

Me.TxtAgency.Value = ""

Me.TxtFAD.Value = ""

Me.TxtDate.Value = ""

Me.TxtNPL.Value = ""

Me.TxtFF.Value = ""

Me.TxtInitiative.Value = ""

Me.TxtRPM.Value = ""

Me.TxtDocDate.Value = ""

Me.TxtPA.Value = ""

Me.TxtSI.Value = ""

Me.TxtFile.Value = ""

Me.TxtUpdate.Value = ""

Me.TxtSite.SetFocus

End Sub

Private Sub cmdClose_Click()

  Unload Me

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub Label10_Click()

End Sub

Private Sub Label14_Click()

End Sub

Private Sub Label8_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _

  CloseMode As Integer)

  If CloseMode = vbFormControlMenu Then

    Cancel = True

    MsgBox "Please use the button!"

  End If

End Sub

Answer 1

It should work, if the code is in the Userform module, and txtSite exist!

If you keep getting errors, you can mail me the workbook at PerJessen69atHotmaildotCom

Answer 2

so, i put the code you recommended into my userform, but i keep getting errors.  the error highlights the "Me" in the check for part number expression in the code and says "compile error."  any idea as to what i'm doing wrong?  thanks!

Answer 3

I would use a txtSite_Change event to populate the userform if a part number is found in the database and then use the cmdUpdate_Click event to update the sheet:

Private Sub cmdUpdate_Click()

Dim iRow As Long

Dim tb As MSForms.Control

Dim ws As Worksheet

Set ws = Worksheets("Active")

'check for a part number

If Trim(Me.txtSite.Value) = "" Then

  Me.txtSite.SetFocus

  MsgBox "Please enter a site name"

  Exit Sub

End If

'Search for part number

Set f = ws.Columns(1).Find(what:=Me.txtSite.Value, lookat:=xlWhole)

If f Is Nothing Then

    msg = MsgBox("Sitename could not be found", vbExclamation + vbOKOnly, "Input error")

    Exit Sub

Else

    iRow = f.Row

End If

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.txtSite.Value

ws.Cells(iRow, 2).Value = Me.txtAddress.Value

ws.Cells(iRow, 3).Value = Me.txtState.Value

ws.Cells(iRow, 4).Value = Me.TxtZip.Value

ws.Cells(iRow, 5).Value = Me.TxtID.Value

ws.Cells(iRow, 6).Value = Me.TxtAgency.Value

ws.Cells(iRow, 7).Value = Me.TxtFAD.Value

ws.Cells(iRow, 8).Value = Me.TxtDate.Value

ws.Cells(iRow, 9).Value = Me.TxtNPL.Value

ws.Cells(iRow, 10).Value = Me.TxtFF.Value

ws.Cells(iRow, 11).Value = Me.TxtInitiative.Value

ws.Cells(iRow, 12).Value = Me.TxtRPM.Value

ws.Cells(iRow, 13).Value = Me.TxtDocDate.Value

ws.Cells(iRow, 14).Value = Me.TxtPA.Value

ws.Cells(iRow, 15).Value = Me.TxtSI.Value

ws.Cells(iRow, 16).Value = Me.TxtFile.Value

ws.Cells(iRow, 17).Value = Me.TxtUpdate.Value

'clear the data

For Each tb In Me.Controls

    If TypeName(tb) = "TextBox" Then

        tb.Value = ""

    End If

Next

Me.txtSite.SetFocus

End Sub

Private Sub txtSite_Change()

Dim ws As Worksheet

Set ws = Worksheets("Active")

Set f = ws.Columns("A").Find(what:=Me.txtSite, lookat:=xlWhole)

If Not f Is Nothing Then

    iRow = f.Row

    Me.txtAddress.Value = ws.Cells(iRow, 2).Value

    Me.txtState.Value = ws.Cells(iRow, 3).Value

    Me.TxtZip.Value = ws.Cells(iRow, 4).Value

    Me.TxtID.Value = ws.Cells(iRow, 5).Value

    Me.TxtAgency.Value = ws.Cells(iRow, 6).Value

    Me.TxtFAD.Value = ws.Cells(iRow, 7).Value

    Me.TxtDate.Value = ws.Cells(iRow, 8).Value

    Me.TxtNPL.Value = ws.Cells(iRow, 9).Value

    Me.TxtFF.Value = ws.Cells(iRow, 10).Value

    Me.TxtInitiative.Value = ws.Cells(iRow, 11).Value

    Me.TxtRPM.Value = ws.Cells(iRow, 12).Value

    Me.TxtDocDate.Value = ws.Cells(iRow, 13).Value

    Me.TxtPA.Value = ws.Cells(iRow, 14).Value

    Me.TxtSI.Value = ws.Cells(iRow, 15).Value

    Me.TxtFile.Value = ws.Cells(iRow, 16).Value

    Me.TxtUpdate.Value = ws.Cells(iRow, 17).Value

End If

End Sub