VBA: Excel form that updates current data on the same worksheet

[justinp]

Hi there,

I'm a novice with VBA and slowly improving my skills.

I've created an Excel VBA form that populates fields based on a combo Box (drop down). I can get this form to insert new records, but I can't get it to update an existing record.
Could someone please help me with the code? Code below:

Private Sub cmdSave_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("lookuptavernlists")
    
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
    
    'copy the data to the database / worksheet
    ws.Cells(iRow, 1).Value = Me.cboTavern.Value
    ws.Cells(iRow, 2).Value = Me.txtFirstname.Value
    ws.Cells(iRow, 3).Value = Me.txtSurname.Value
    ws.Cells(iRow, 4).Value = Me.txtCell.Value
    ws.Cells(iRow, 5).Value = Me.txtSecurityQ.Value
    ws.Cells(iRow, 6).Value = Me.txtSecurityA.Value
    ws.Cells(iRow, 7).Value = Me.Register.Value
    
    'clear the data
    Me.cboTavern.Value = ""
    Me.txtFirstname.Value = ""
    Me.txtSurname.Value = ""
    Me.txtCell.Value = ""
    Me.txtSecurityQ.Value = ""
    Me.txtSecurityA.Value = ""
    Me.Register.Value = ""
    Me.txtFirstname.SetFocus

End Sub


Private Sub UserForm_Initialize()
    
    Dim cTavern As Range
    Dim ws As Worksheet
    Set ws = Worksheets("lookuptavernlists")
    
    For Each cTavern In ws.Range("TavernList")
      With Me.cboTavern
        .AddItem cTavern.Value
        .List(.ListCount - 1, 1) = cTavern.Offset(0, 1).Value
      End With
    Next cTavern
    
End Sub

Private Sub cboTavern_Change()

    txtFirstname.Value = Range("B" & cboTavern.ListIndex + 2)
    txtSurname.Value = Range("C" & cboTavern.ListIndex + 2)
    txtCell.Value = Range("D" & cboTavern.ListIndex + 2)

End Sub
Private Sub cmdClose_Click()
    SAB_Registration_Form.Hide
End Sub

 

[justinp]

Does anyone have any update VBA form code?

I can insert new form records to a worksheet, but I cant seem to update existing records.

Any ideas?

 

[John_w]

I see that your cboTavern.Value is put in column A. Therefore search this column for the selected cboTavern.Value, either using a VBA While or Do loop (row by row) or the Range Find method, and if found update the cells on that row. For the Find method, generate the VBA code with the macro recorder whilst selecting column A and doing a manual Edit - Find.

 

[justinp]

Thanks John_w