August 2019 – Free Excel Training 2022

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:

User-form to Manage Data Automatically

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 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

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 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

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

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

Free Excel Training 2022

Excel Training Tutorials 2022

Month: August 2019

Userform to Manage Data Automatically