July 31, 2014

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:

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

  2. Dear Dinish Sir,

    I have a dilemma at work whereby I have 5 systems generating 5 different debtor reports. I need to group customers from all 5 debtor reports and generate one account number using same VAT number. Example companies ABC ltd, def ltd and ghi ltd share the same Vat number so I want to automatically extract data from all 5 Sheets and generate a group account number. Also if any new accounts appear they need to be automatically picked up. Could you please help me with this challenge?

    Thank you in advance

    Sayeed

  3. Good morning sir, please I have been following your training and it’s has helped me alot.
    Please I have a problem with this
    Private sub userform _ initialize ()
    Worksheets ( “ Main Database ” ). Select
    *ChkBclub = false*(variable not defined *)
    chkTennis = False
    chkGuitar = False
    chkKeyboard = False
    ChkCvisits
    Please help me

Comments are closed.