How to manage data in Excel automatically, quickly and easily with an advanced user-form. We will learn how to add data, navigate to the next and previous data record, view all data in a list-box, delete a record, update a record, clear the user-form data in text boxes, image control, combo-box, etc. and close the form once the work is done. Watch the video below:
Watch this video on YouTube.
Properties of List Box

Here’s the complete VBA code to manage data using a user-form in Excel:
Option Explicit
Dim lr As Long
Dim activerow As Long
Private Sub cmdAllData_Click()
ListBox1.Visible = True
Me.ListBox1.RowSource = “sheet1!A2:C” & lr
End Sub
Private Sub cmdClear_Click()
txtFirstName = “”
txtLastName = “”
txtEmail = “”
Me.ComboBox1 = “”
Me.Image1.Picture = Nothing
Me.cmdDelete.Enabled = False
Me.TextBox2.Visible = False
‘MsgBox “Data Cleared!”
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdDelete_Click()
Dim fvalue As Range
Dim fpath As String
fpath = ThisWorkbook.Path & “\”
Set fvalue = Sheet1.Range(“A:A”).Find(what:=Me.ComboBox1, LookIn:=xlFormulas, lookat:=xlWhole)
Select Case MsgBox(“Do you really want to delete ” & Me.ComboBox1.Text & “‘s” & ” data?”, vbYesNo, “Confirm Delete”)
Case Is = vbNo
Exit Sub
Case Is = vbYes fvalue.EntireRow.Delete
On Error Resume Next
Kill (fpath & Me.txtFirstName.Text & “.jpg”)
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.RowSource = “sheet1!A2:A” & lr
MsgBox “Data deleted!”
Call cmdClear_Click
End Select
End Sub
Private Sub cmdInsert_Click()
Dim imagepath As String
‘Dim imagename As String
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = ThisWorkbook.Path .Filters.Clear .Filters.Add "JPEG", "*.jpg, *.jpeg" .Filters.Add "PNG", "*.png" .Filters.Add "GIF", "*.gif" .Title = "Insert Image" .ButtonName = "Select Image" .AllowMultiSelect = False If .Show = True Then Me.TextBox2.Visible = True imagepath = .SelectedItems(1) Me.TextBox2.Text = imagepath Me.Image1.Picture = LoadPicture(imagepath) Else MsgBox "Image not selected" End If End With
End Sub
Private Sub cmdMultiSearch_Click()
Dim fpath As String
fpath = ThisWorkbook.Path & “\”
Dim findvalue As Range, findvalue2 As Range, findvalue3 As Range
Set findvalue = Sheet1.Range(“A:A”).Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
Set findvalue2 = Sheet1.Range(“B:B”).Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
Set findvalue3 = Sheet1.Range(“C:C”).Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If Me.ComboBox1.Value <> “” Then
If WorksheetFunction.CountIf(Sheets("sheet1").Range("A:A"), Me.ComboBox1.Text) > 0 Then Me.txtFirstName = findvalue Me.txtLastName = findvalue.Offset(0, 1) Me.txtEmail = findvalue.Offset(0, 2) Me.cmdUpdate.Enabled = True Me.cmdDelete.Enabled = True activerow = findvalue.Row
With findvalue
On Error Resume Next
Me.Image1.Picture = LoadPicture(fpath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(fpath & Me.txtFirstName.Text & ".jpg")
End With
ElseIf WorksheetFunction.CountIf(Sheets(“sheet1”).Range(“B:B”), Me.ComboBox1.Text) > 0 Then
Me.txtLastName = findvalue2
Me.txtFirstName = findvalue2.Offset(0, -1)
Me.txtEmail = findvalue2.Offset(0, 1)
Me.cmdDelete.Enabled = False Me.cmdUpdate.Enabled = False activerow = findvalue2.Row
With findvalue
On Error Resume Next
Me.Image1.Picture = LoadPicture(fpath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(fpath & Me.txtFirstName.Text & ".jpg")
End With
ElseIf WorksheetFunction.CountIf(Sheets(“sheet1”).Range(“C:C”), Me.ComboBox1.Text) > 0 Then
Me.txtEmail = findvalue3 Me.txtLastName = findvalue3.Offset(0, -1) Me.txtFirstName = findvalue3.Offset(0, -2) Me.cmdDelete.Enabled = False Me.cmdUpdate.Enabled = False activerow = findvalue3.Row
With findvalue
On Error Resume Next
Me.Image1.Picture = LoadPicture(fpath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(fpath & Me.txtFirstName.Text & ".jpg")
End With
Else
MsgBox “Data not found!”
End If
End If
End Sub
Private Sub cmdNext_Click()
Dim pName As Range
Dim imagepath As String
imagepath = ThisWorkbook.Path & “\”
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
activerow = activerow + 1
‘MsgBox activerow
‘If activerow = lr + 1 Then
If activerow = lr + 1 Then
activerow = lr
MsgBox “You have reached the last data row”
End If
With Cells(activerow, 1)
Me.txtFirstName.Text = Cells(activerow, 1) Set pName = .Find(Me.txtFirstName.Text)
With pName
On Error Resume Next
Me.Image1.Picture = LoadPicture(imagepath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(imagepath & Me.txtFirstName.Text & “.jpg”)
End With
End With
txtFirstName = Cells(activerow, 1)
txtLastName = Cells(activerow, 2)
txtEmail = Cells(activerow, 3)
End Sub
Private Sub cmdPrev_Click()
Dim pName As Range
Dim imagepath As String
imagepath = ThisWorkbook.Path & “\”
If txtFirstName = “” Or txtLastName = “” Or txtEmail = “” Then
MsgBox “Please search data or enter all data!”
Exit Sub
End If
activerow = activerow – 1
If activerow >= 2 Then
txtFirstName = Cells(activerow, 1)
txtLastName = Cells(activerow, 2)
txtEmail = Cells(activerow, 3)
ElseIf activerow = 1 Then
activerow = activerow + 1
MsgBox “You have reached the first data”
End If
With Cells(activerow, 1)
Me.txtFirstName.Text = Cells(activerow, 1) Set pName = .Find(Me.txtFirstName.Text)
With pName
On Error Resume Next
Me.Image1.Picture = LoadPicture(imagepath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(imagepath & Me.txtFirstName.Text & ".jpg")
End With
End With
End Sub
Private Sub cmdSearch_Click()
Dim fpath As String
fpath = ThisWorkbook.Path & “\”
If Me.ComboBox1.Value <> “” Then
Dim findvalue As Range
Set findvalue = Sheet1.Range(“A:A”).Find(what:=Me.ComboBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If WorksheetFunction.CountIf(Sheets(“sheet1”).Range(“A:A”), Me.ComboBox1.Text) > 0 Then
Me.txtFirstName = findvalue
Me.txtLastName = findvalue.Offset(0, 1)
Me.txtEmail = findvalue.Offset(0, 2)
Me.cmdDelete.Enabled = True
With findvalue
On Error Resume Next
Me.Image1.Picture = LoadPicture(fpath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(fpath & Me.txtFirstName.Text & ".jpg")
End With
End If
End If
activerow = findvalue.Row
Me.cmdUpdate.Enabled = True
End Sub
Private Sub cmdUpdate_Click()
Dim f_path As String
f_path = ThisWorkbook.Path & “\”
Dim fvalue As Range
Set fvalue = Sheet1.Range(“A:A”).Find(what:=Me.ComboBox1, LookIn:=xlFormulas, lookat:=xlWhole)
If Me.txtFirstName = “” Or Me.txtLastName = “” Or Me.txtEmail = “” Then
MsgBox “All data must be entered!”
Exit Sub
End If
fvalue = Me.txtFirstName fvalue.Offset(0, 1) = Me.txtLastName fvalue.Offset(0, 2) = Me.txtEmail
On Error Resume Next
FileCopy Me.TextBox2, f_path & Me.txtFirstName & “.jpg”
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row Me.ComboBox1.RowSource = “sheet1!A:A” & lr
Call cmdClear_Click
Me.cmdDelete.Enabled = False
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim li As Long
Dim fpath As String
Dim findvalueL As Range
fpath = ThisWorkbook.Path & “\”
li = Me.ListBox1.ListIndex
If Me.ListBox1 <> “” Then
Set findvalueL = Sheet1.Range(“A:A”).Find(what:=Me.ListBox1.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If WorksheetFunction.CountIf(Sheets(“sheet1”).Range(“A:A”), Me.ListBox1.Text) > 0 Then Me.txtFirstName = findvalueL Me.txtLastName = findvalueL.Offset(0, 1) Me.txtEmail = findvalueL.Offset(0, 2) Me.cmdDelete.Enabled = True
With findvalueL
On Error Resume Next
Me.Image1.Picture = LoadPicture(fpath & “no-image.jpg”)
Me.Image1.Picture = LoadPicture(fpath & Me.txtFirstName.Text & “.jpg”)
End With
End If
End If
activerow = findvalueL.Row
ListBox1.Visible = False
Me.cmdDelete.Enabled = False
End Sub
Private Sub optEmail_Click()
Me.ComboBox1 = “”
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.RowSource = “sheet1!C2:C” & lr
End Sub
Private Sub optFirstName_Click()
Me.ComboBox1 = “”
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.RowSource = “sheet1!A2:A” & lr
End Sub
Private Sub cmdSend_Click()
Dim f_path As String
f_path = ThisWorkbook.Path & “\”
If Me.txtFirstName = “” Or Me.txtLastName = “” Or Me.txtEmail = “” Then
MsgBox “Please enter data in all textboxes”
Exit Sub
End If
If WorksheetFunction.CountIf(Sheets(“sheet1”).Range(“A:A”), Me.txtFirstName.Text) > 0 Then
MsgBox “This name already exists in the database!”
Exit Sub
End If
Dim nextBlankRow As Long
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
nextBlankRow = lr + 1
Range(“A” & nextBlankRow) = txtFirstName
Range(“B” & nextBlankRow) = txtLastName
Range(“C” & nextBlankRow) = txtEmail
On Error Resume Next
FileCopy Me.TextBox2, f_path & Me.txtFirstName.Text & “.jpg”
txtFirstName = “”
txtLastName = “”
txtEmail = “”
Me.ComboBox1.RowSource = “sheet1!A2:A” & lr + 1
MsgBox “Data added sucessfully.”
Call cmdClear_Click
End Sub
Private Sub optLastName_Click()
Me.ComboBox1 = “”
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Me.ComboBox1.RowSource = “sheet1!B2:B” & lr
End Sub
Private Sub UserForm_Initialize()
Me.cmdDelete.Enabled = False
Me.TextBox2.Visible = False
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
activerow = 1
Me.ComboBox1.RowSource = “sheet1!A2:A” & lr
End Sub
I have a mini personal project and would like to seek your guidance and support in terms of the coding part. Can i ask your contact details so that i can show you the framework.
SIR
USERFORM INITILAISE NOT MENTION INIT
ROW SOURCE CAN WE GIVE NAME FROM NAME MANEGER
Send me file
Can please get the training soft copy
sir, lots of error are occuring time time .
Please can u share the excel file with vba code .
its a humble request please
E-mail : [email protected]
code generating errors, could you share code
send me file sir
getting an error
activerow = findvalueL.Row in
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
appreciate your advice
salam
Please can u share the excel file with vba code .
its a humble request please
E-mail : [email protected]
REPLY
Hi I would highly appreciate if you are able to send the excel file. [email protected]
Plz send the files
Please can you send a copy of the excel workbook
[email protected]
Hello. I’m Brazilian, sorry for the language errors. Is your spreadsheet for sale or can you please send it to me? I would like to study it. Follow my email: [email protected] Thank you very much!
Your video makes me feel it so easy but it is indeed a lot of effort. Thanks for your guidance and sharing knowledge. Like your energy level too while explaining.
To expedite and suit my requirement, is it possible that I have your excel file so that I can tweet the fields and requirement.
I shall be highly obliged if you can email to me.
Thank you and success.
Cheers!
Your video makes me feel it so easy but it is indeed a lot of effort. Thanks for your guidance and sharing knowledge. Like your energy level too while explaining.
To expedite and suit my requirement, is it possible that I have your excel file so that I can tweet the fields and requirement.
I shall be highly obliged if you can email to me.
Thank you and success.
Cheers!
Your video makes me feel it so easy but it is indeed a lot of effort. Thanks for your guidance and sharing knowledge. Like your energy level too while explaining.
To expedite and suit my requirement, is it possible that I have your excel file so that I can tweet the fields and requirement.
I shall be highly obliged if you can email to me.
Thank you and success.
Cheers!