How To Create An Excel Userform To Add Edit And Delete

This post is going to show you how to create a UserForm in Excel which will allow you to do
the most common tasks of add, edit, and delete of records.

Here is the code for the form above:

'Erik Loebl
'https://vbastring.com
'erik@loeblcomservices.com

'We will make a modular scope variable because we need this _
    row reference as long as the form is open

Dim m_lngRow As Long




Private Sub btnForward_Click()
    
    'increment the active row and sync the UserForm and the Activesheet
    m_lngRow = ActiveCell.Row + 1
    
    'select the next cell and render the new data on the UserForm
    Range("A" & m_lngRow).Select
    Me.txtCustomerID = Range("A" & m_lngRow)
    Me.txtCompanyName = Range("B" & m_lngRow)
    Me.txtContactName = Range("C" & m_lngRow)
    Me.txtContactTitle = Range("D" & m_lngRow)
    Me.txtAddress = Range("E" & m_lngRow)
    
End Sub


Private Sub btnBack_Click()
    
    'decrement the active row and sync the UserForm and the Activesheet
    m_lngRow = ActiveCell.Row - 1
    
    'select the next cell and render the new data on the UserForm
    Range("A" & m_lngRow).Select
    Me.txtCustomerID = Range("A" & m_lngRow)
    Me.txtCompanyName = Range("B" & m_lngRow)
    Me.txtContactName = Range("C" & m_lngRow)
    Me.txtContactTitle = Range("D" & m_lngRow)
    Me.txtAddress = Range("E" & m_lngRow)
    
End Sub

Private Sub btnEnter_Click()
    Range("A" & ActiveCell.Row) = Me.txtCustomerID
    Range("B" & ActiveCell.Row) = Me.txtCompanyName
    Range("C" & ActiveCell.Row) = Me.txtContactName
    Range("D" & ActiveCell.Row) = Me.txtContactTitle
    Range("E" & ActiveCell.Row) = Me.txtAddress

End Sub




Private Sub btnNew_Click()
    Dim lngLastRow As Long
    
    'Find the last row used
    lngLastRow = FindLastRow("A")
    
    lngLastRow = lngLastRow + 1
    Range("A" & lngLastRow).Select
    
    Me.txtCustomerID = Range("A" & lngLastRow)
    Me.txtCompanyName = Range("B" & lngLastRow)
    Me.txtContactName = Range("C" & lngLastRow)
    Me.txtContactTitle = Range("D" & lngLastRow)
    Me.txtAddress = Range("E" & lngLastRow)
    
End Sub

Private Sub btnDelete_Click()
    
    Dim lngRow As Long
 
    lngRow = Selection.Row
    Rows(lngRow).EntireRow.Delete
    
    
End Sub
Private Sub btnEdit_Click()
    
    Range("A" & ActiveCell.Row) = Me.txtCustomerID
    Range("B" & ActiveCell.Row) = Me.txtCompanyName
    Range("C" & ActiveCell.Row) = Me.txtContactName
    Range("D" & ActiveCell.Row) = Me.txtContactTitle
    Range("E" & ActiveCell.Row) = Me.txtAddress

End Sub


Function FindLastRow(WhichColumn As String) As Long

    Dim lngLastRow As Long
    
    'move to the last row on the worksheet and find the last used cell.
    
    With ActiveSheet
        lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row
    End With

    FindLastRow = lngLastRow

End Function

Private Sub UserForm_Initialize()
    Range("A2").Select
    Me.txtCustomerID = Range("A2")
    Me.txtCompanyName = Range("B2")
    Me.txtContactName = Range("C2")
    Me.txtContactTitle = Range("D2")
    Me.txtAddress = Range("E2")
End Sub

The addition, edit, and delete operations are in these 3 simple procedures:

Private Sub btnNew_Click()
    Dim lngLastRow As Long
    
    'Find the last row used
    lngLastRow = FindLastRow("A")
    
    lngLastRow = lngLastRow + 1
    Range("A" & lngLastRow).Select
    
    Me.txtCustomerID = Range("A" & lngLastRow)
    Me.txtCompanyName = Range("B" & lngLastRow)
    Me.txtContactName = Range("C" & lngLastRow)
    Me.txtContactTitle = Range("D" & lngLastRow)
    Me.txtAddress = Range("E" & lngLastRow)
    
End Sub

Private Sub btnDelete_Click()
    
    Dim lngRow As Long
 
    lngRow = Selection.Row
    Rows(lngRow).EntireRow.Delete
    
    
End Sub
Private Sub btnEdit_Click()
    
    Range("A" & ActiveCell.Row) = Me.txtCustomerID
    Range("B" & ActiveCell.Row) = Me.txtCompanyName
    Range("C" & ActiveCell.Row) = Me.txtContactName
    Range("D" & ActiveCell.Row) = Me.txtContactTitle
    Range("E" & ActiveCell.Row) = Me.txtAddress

End Sub

Here is the workbook to download and take a look at.

excel userform add edit delete.xlsm

Let me know if you need help.

****************************************************