Database and Customer Relationship Management with Excel VBA Video 1


Many users wish to know how to create a database and perform a customer relationship management in Excel using a userform with VBA. A typical query may look like so:

A workbook contains the following sheets:

1) Main Database
2) Activities Sheets as follows:
a) Book Club
b) Tennis
c) Guitar
d) Keyboard
e) Care Visits

Can we use a User-form to make both the entries in the Main Database and the Activities Sheets.

If, for example, Ms Joan Thomas who is currently registered for both Tennis and Care Visits decides in future to register for, say, Keyboard, how can we call up her details (using the UserForm), and be able to UPDATE her records i.e. registering her for Keyboard – including the date of her registration.

Can we also automate the process to throw up a Birthday reminder for members and send them a ‘happy birthday’ greeting automatically.

First we’ll create a userform with a combobox, textboxes, checkboxes and command buttons. Now the main work lies in the coding of the command buttons to:

  • Transfer data from the userform to the Excel worksheet
  • Display the data from the Excel worksheet on the user-form
  • Navigate to the records in the Excel worksheet via the user-form both ways – backwards (previous records) and forwards (next record)
  • Update records if mistakes have been made during data entry or new activity for a member needs to be added
  • Send email birthday greetings to members automatically using Outlook

If you get a duplicate entry or entries in your Excel worksheet, then you can run a macro to remove the earlier duplicate entries and keep the latest updated entry of the member.

Watch the first video of a series of 4 videos. This first video describes how to transfer the data from the UserForm to the Excel worksheets:


View the video on YouTube

Study the VBA code below to see how the data is transferred from the userform to the multiple Excel worksheets step by step:

Private Sub cmdData_Click()
Worksheets(“Main Database”).Select
erow = Sheets(“Main Database”).Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
txtDob.Text = Format(txtDob.Text, “dd-mmm”)
Cells(erow, 6) = txtDob.Text

If chkBclub Then
Worksheets(“Book Club”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

If chkTennis Then
Worksheets(“Tennis”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If
If chkGuitar Then
Worksheets(“Guitar”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If
If chkKeyboard Then
Worksheets(“Keyboard”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If
If chkCvisits Then
Worksheets(“care Visits”).Select
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = cboTitle.Text
Cells(erow, 2) = txtFname.Text
Cells(erow, 3) = txtLname.Text
Cells(erow, 4) = txtPhone.Text
Cells(erow, 5) = txtEmail.Text
Cells(erow, 6) = Date
End If

Worksheets(“Main Database”).Select

End Sub

Note: When the userform fires up it is also initialized with certain values using VBA code which is described in Video in great detail.

Download a sample file by clicking on the Excel icon:

Leave a Reply

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