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.
****************************************************
|
Comments
One response to “How To Create An Excel Userform To Add Edit And Delete”