Database and Customer Relationship Management with Excel VBA Video 3


This third video on database and customer relationship management with Excel VBA and UserForm explains how to display  data from multiple Excel worksheets on a userform which includes a combobox, textboxes and checkboxes.

Here’s the complete VBA code which you can study after watching the video:

Private Sub UserForm_Initialize()
Worksheets(“Main Database”).Select
chkBclub = False
chkBclub = False
chkTennis = False
chkGuitar = False
chkKeyboard = False
chkCvisits = False
currentrow = 2
cboTitle.List = Array(“Ms”, “Mrs”, “Mr”, “Dr”, “Prof”)
cboTitle.Text = Cells(currentrow, 1)
txtFname.Text = Cells(currentrow, 2)
txtLname.Text = Cells(currentrow, 3)
txtPhone.Text = Cells(currentrow, 4)
txtEmail.Text = Cells(currentrow, 5)
txtDob.Text = Cells(currentrow, 6)
cboTitle.SetFocus

Dim fname As String, lname As String
fname = txtFname.Text
lname = txtLname.Text

For q = 1 To Sheets.count
Dim x As Long, lastrow As Long

lastrow = Sheets(q).Range(“A” & Rows.count).End(xlUp).Row
x = 2

For x = 2 To lastrow
If Sheets(q).Cells(x, 2) = fname And Sheets(q).Cells(x, 3) = lname And Sheets(q).Name = “Book Club” Then
UserForm1.chkBclub = True
End If

If Sheets(q).Cells(x, 2) = fname And Sheets(q).Cells(x, 3) = lname And Sheets(q).Name = “Tennis” Then
UserForm1.chkTennis = True
End If

If Sheets(q).Cells(x, 2) = fname And Sheets(q).Cells(x, 3) = lname And Sheets(q).Name = “Guitar” Then
UserForm1.chkGuitar = True

End If

If Sheets(q).Cells(x, 2) = fname And Sheets(q).Cells(x, 3) = lname And Sheets(q).Name = “KeyBoard” Then
UserForm1.chkKeyboard = True

End If

If Sheets(q).Cells(x, 2) = fname And Sheets(q).Cells(x, 3) = lname And Sheets(q).Name = “Care Visits” Then
UserForm1.chkCvisits = True
End If

Next x

Next q

End Sub

Watch the training video to learn quickly and easily how to display data from multiple worksheets on a userform:


View the video on YouTube

Download a sample file by clicking on the Excel icon:

One thought on “Database and Customer Relationship Management with Excel VBA Video 3

  1. Muhsin

    Hi Sir,
    may i know how q comes in looping?
    is there any declaration is needed for looping? or “Q”
    i did’t get the check boxes true while running the form

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *