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:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

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

Leave a Reply

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