Introduction to Excel VBA and CRM Tools

Customer Relationship Management (CRM) tools are integral for businesses to manage interactions with customers and potential customers. To demonstrate how to construct a CRM tool using Excel VBA (Visual Basic for Applications), you will first need to install either Microsoft Excel 2016, Office 365 or similar, as any of these versions support VBA.

VBA is a programming language used for automating tasks in Excel. Its integration within Excel allows users to create user-defined functions, access Windows API, automate specific computer processes, and perform other tasks.

Section One: Introduction to VBA in Excel

1.1 Activating Developer Tab

The journey of becoming proficient with Excel VBA begins by activating the Developer tab. Below are steps to do this:

• Open Microsoft Excel
• Go to File, then Options
• Select Customize Ribbon
• Check the Developer box, then click OK.

Once this is done, the Developer tab is available in the Excel Ribbon which will give access to VBA.

1.2 Basics of Programming with VBA

To start programming with VBA, follow the steps listed below:

• Click on Developer Tab -> Visual Basic -> Insert -> Module. This will create a new module.
• Start by typing your sub-procedure or function within this module.

An example sub-procedure that writes "Hello, World!" in the first cell of the active worksheet:

Sub HelloWorld()
    ActiveSheet.Cells(1, 1).Value = "Hello, World!"
End Sub

You can run your VBA code by pressing F5 within the VBA editor (ensure your code is highlighted before pressing F5).

Section Two: Introduction to CRM Tools

CRM Tools are vital for businesses of all sizes, enabling them to manage their interactions with current and potential customers.

2.1 What are CRM Tools

CRM Tools manage and analyze interactions with customers, aiming to improve customer relations and drive sales growth. Features of a CRM tool may include contact management, sales management, productivity, and more.

Section Three: Constructing a CRM Tool with Excel VBA

Below, we create a simple Contact Management System using Excel VBA as our CRM tool.

3.1 Creating Userform

Within Visual Basic for Applications (VBA):

• Click on Insert -> UserForm in the VBA Editor to add a new UserForm.
• On the Toolbox, select the TextBox tool, then click where you want to add the first text box on the form and drag to its desired size. Repeat this step for all necessary text boxes.
• Label your text boxes accordingly using Label tools.

3.2 VBA Code to Add Contacts

Below is a simple example of VBA code that adds contact details inputted from a UserForm into the next empty row of a worksheet:

Private Sub cmdAdd_Click()
    Dim LastRow As Long
    With ThisWorkbook.Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LastRow, 1).Value = txtFirstName.Value
        .Cells(LastRow, 2).Value = txtLastName.Value
        .Cells(LastRow, 3).Value = txtEmail.Value
        'Add additional lines as needed for more fields
    End With
    MsgBox "Contact added."
End Sub

In this simple CRM example, we have introduced the basics of Excel VBA and shown how it can be paired with UserForms to create basic CRM functionality. Progressing further would involve adding more complex features such as data validation, advanced UI features, and more complex data management functions. For real world applications, make sure to keep the principles of data integrity and security in mind as you expand on these basics.

VBA Programming Fundamentals for CRM Tool Construction

In this section, we will demonstrate practical uses of VBA (Visual Basic for Applications) to construct a CRM (Customer Relationship Management) tool.

Section 1: Setting Up the User Form

We will start by creating a user form that will collect and store customer information.

Private Sub UserForm_Initialize()
   'Define and load combo box options
   cmbState.List = Array("New York", "California", "Florida")
End Sub

Private Sub cmdAdd_Click()
    'Add the contact details to the worksheet
    Worksheets("Contacts").Cells(Application.End(xlUp).Row + 1, 1) = txtName.Value
    Worksheets("Contacts").Cells(Application.End(xlUp).Row, 2) = txtEmail.Value
    Worksheets("Contacts").Cells(Application.End(xlUp).Row, 3) = txtPhone.Value
    Worksheets("Contacts").Cells(Application.End(xlUp).Row, 4) = cmbState.Value
    ' Clear the form
    txtName.Value = ""
    txtEmail.Value = ""
    txtPhone.Value = ""
    cmbState.Value = ""
End Sub

Section 2: Data Validation

We must ensure that the user inputs the correct and appropriate data into the form we just created. We will utilize the Regex object to easily check the validity of the email and phone number entered by the user.

Function ValidateEmail(ByVal strValue As String) As Boolean
   Dim regEx As New RegExp
   With regEx
      .Pattern = "^[\w.-]+@([\w-]+\.)+[\w-]{2,6}$"
      .IgnoreCase = True
      .Global = True
      If .Test(strValue) Then
         ValidateEmail = True
      Else
         ValidateEmail = False
      End If
   End With
End Function

Function ValidatePhone(ByVal strValue As String) As Boolean
   Dim regEx As New RegExp
   With regEx
      .Pattern = "^\D?(\d{3})\D?\D?(\d{3})\D?(\d{4})$"
      .IgnoreCase = True
      .Global = True
      If .Test(strValue) Then
         ValidatePhone = True
      Else
         ValidatePhone = False
      End If
   End With
End Function

Private Sub cmdAdd_Click()
   ' Validate email
   If Not ValidateEmail(txtEmail.Value) Then
       MsgBox "Please enter a valid email address"
       Exit Sub
   End If
   
   ' Validate phone
   If Not ValidatePhone(txtPhone.Value) Then
       MsgBox "Please enter a valid phone number"
       Exit Sub
   End If
   ' ... continue with code from previous section
End Sub

Section 3: Data Search Functionality

We'll create a simple search function to find and retrieve customer data.

Private Sub cmdSearch_Click()
    Dim name As String
    Dim finalRow As Integer
    Dim i As Integer

    'Get the name to search for
    name = txtSearch.Value
    finalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row

    'Loop through each row
    For i = 2 To finalRow
        'If the cell value in our loop is equal to our search value, then highlight the cell
        If Cells(i, 1).Value = name Then
            Rows(i).Select
        End If
    Next i
End Sub

In this code, we loop through each row and compare the customer's name in that row with our search term.

Use these ready-to-use VBA functions in your CRM project. Make sure you've properly set up your Excel VBA environment before applying these code snippets. Note that each code block provided here adds new functionality to your CRM Excel tool and they are committed to being user-driven and responsive.

1. Overview

In this section, I will be focusing on designing a user-friendly Input Form using Excel VBA as a part of your ongoing project. This Form will be used to input potential customer data into your CRM Tool.

2. Starting VBA

To begin, press ALT + F11 to open the VBA Editor in Excel. In the Project Explorer window, you'll generally find a folder named Forms. If it's not there, we'll have to create one. Now, click on Insert from menu -> UserForm to add a new Form.

3. Designing the Form Layout

A Form with no controls will appear. To design your Form, we will use the Toolbox panel. If it's not visible, select View from the menu, then Toolbox to toggle it on/off. For this exercise, we will add TextBoxes for user input and Labels to clearly identify what data should be entered. We will also add a Command Button to submit the data. Here is the VBA code for creating a simple form with name, email and phone number.

Code:

With Me
    .Width = 350
    .Height = 200
    .Caption = "Data Input Form"

    'Adding Name TextBox
    With .Controls.Add("Forms.TextBox.1", "txtName", True)
        .Width = 100
        .Height = 20
        .Left = 200
        .Top = 15
        .Font.Size = 10
    End With 

    'Adding Name Label
    With .Controls.Add("Forms.Label.1", "lblName", True)
        .Caption = "Name"
        .Width = 60
        .Height = 18
        .Left = 15
        .Top = 15
    End With 

    'Repeat above steps for Email and Phone, adjust .Top for each to place them correctly.
    'For email TextBox - txtEmail and Label - lblEmail, .Top can be 50.
    'For phone TextBox - txtPhone and Label - lblPhone, .Top can be 85.

    'Adding Submit Button
    With .Controls.Add("Forms.CommandButton.1", "btnSubmit")
        .Caption = "Submit"
        .Width = 100
        .Height = 24
        .Left = 120
        .Top = 125
    End With 
End With

This code, when run, will create a form with TextBoxes for Name, Email, Phone, and a Submit button.

4. Data Entry Functionality

We want to store the user input in our Excel sheet. For that, we'll create a procedure for the Click event of our Submit button. This procedure will be automatically triggered whenever the button is clicked. To do this, double click on the button in your Form.

Here is the VBA code implementing the Click event:

Code:

Private Sub btnSubmit_Click()
    Dim RowCount As Long

    'Get count of rows in the sheet where data is to be stored.
    RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

    'Populate the data in columns A, B, C of the next empty row.
    Worksheets("Sheet1").Cells(RowCount + 1, 1).Value = txtName.Value
    Worksheets("Sheet1").Cells(RowCount + 1, 2).Value = txtEmail.Value
    Worksheets("Sheet1").Cells(RowCount + 1, 3).Value = txtPhone.Value

    'Reset the text boxes for next entry
    txtName.Value = ""
    txtEmail.Value = ""
    txtPhone.Value = ""
End Sub

With this, your Form will store the entered data in your Excel Sheet "Sheet1" every time the user clicks the Submit button. After saving the data, we reset the input fields for the next entry.

5. Displaying the Form

For the user to view and fill the Form, we need to display it. Usually, it's displayed on Workbook open or by attaching the Form to a button on Worksheet. I'll show you the latter approach.

First, go back to Excel. On the Developer tab, click Button in the Controls group and draw a button on your Worksheet. Assign the following macro to it:

Code:

Sub DisplayForm()
    UserForm1.Show
End Sub

Now, when you click this button, your Form will display. Fill the fields and click Submit to test it. The input data should be stored in Sheet1 and TextBoxes reset for the next entry.

These steps guide you through creating a simple Data Input Form in Excel VBA. Depending on your CRM Tool's requirements, this Form can be further enhanced with more data validation, error handling and custom functionality.

Automating Data Input Using VBA

Automation of data input in Excel not only brings in the dimension of efficiency but also ensures error-free entries. This task can be achieved with the help of Excel's in-built Visual Basic for Applications (VBA).

Creating UserForm for CRM Tool

Assuming that your CRM tool has a structure that consists of fields:

• Customer Name
• Email
• Phone Number
• Address
• Interaction Date
• Follow Up Required

We are going to implement an input box for each of these fields in a UserForm.

Private Sub UserForm_Click()

'Defining the user form
With New UserForm1

    'Creating an input box for Customer Name
    With .Controls.Add("Forms.TextBox.1", "CustomerName", True)
        .Top = 10
        .Left = 10
        .Width = 150
    End With
    
    'Similarly we add boxes for other fields.
    
    'Creating an input box for Email
    With .Controls.Add("Forms.TextBox.1", "Email", True)
    'define the position and size for the box
    End With

    'Creating an input box for Phone Number
    With .Controls.Add("Forms.TextBox.1", "PhoneNumber", True)
    'define the position and size for the box
    End With
    
    'Creating an input box for Address
    With .Controls.Add("Forms.TextBox.1", "Address", True)
    'define the position and size for the box
    End With
    
    'Creating an input box for InteractionDate
    With .Controls.Add("Forms.TextBox.1", "InteractionDate", True)
    'define the position and size for the box
    End With

    'Creating an input box for FollowUpRequired
    With .Controls.Add("Forms.TextBox.1", "FollowUpRequired", True)
    'define the position and size for the box
    End With

    'Show the UserForm
    .Show

End With

End Sub

Handling Entries and Data Validation

Assuming that we have a sheet named "CRMData" where the data will be entered. Implementation can be as follows, using the corresponding row and column where data needs to be filled. Below example assumes next blank row is found.

Private Sub CommandButton1_Click()

    'Creating an object for the CRMData sheet
    Dim CRMData As Worksheet
    Set CRMData = ThisWorkbook.Sheets("CRMData")

    'Checking for blank entries in the required fields, assuming CustomerName and Email are required
    If Me.CustomerName.Value = "" Then
        MsgBox "Please enter Customer Name.", vbExclamation
    ElseIf Me.Email.Value = "" Then
        MsgBox "Please enter an Email.", vbExclamation
    Else
        'After input validation we will proceed with data entry
        Dim LastRow As Long
        
        ' We find the last row
        LastRow = CRMData.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        'Filling in the data
        CRMData.Cells(LastRow, 1).Value = Me.CustomerName.Value
        CRMData.Cells(LastRow, 2).Value = Me.Email.Value
        CRMData.Cells(lastRow, 3).Value = Me.PhoneNumber.Value
        CRMData.Cells(lastRow, 4).Value = Me.Address.Value
        CRMData.Cells(lastRow, 5).Value = Me.InteractionDate.Value
        CRMData.Cells(lastRow, 6).Value = Me.FollowUpRequired.Value

        'Clearing the input boxes after data entry
        Me.CustomerName.Value = ""
        Me.Email.Value = ""
        Me.PhoneNumber.Value= ""
        Me.Address.Value= ""
        Me.InteractionDate.Value= ""
        Me.FollowUpRequired.Value= ""
        
    End If
End Sub

For accurate data entries, appropriate data validation checks should be in place before entering the data into the worksheet.

Closing the UserForm

Add a CommandButton to your UserForm and paste this in its Click event to close the form.

Private Sub CommandButton2_Click()
   Unload Me
End Sub

This way data entry into your CRM tool can be successfully automated. Contents of each UserForm are dynamically entered into the worksheet, reducing manual work. Remember, it's always necessary to implement error handling mechanisms to handle exceptions effectively.

Creating Analysis Page in Excel VBA

In this section, we will create the Analysis Page using Excel VBA which shall serve as a core part of your CRM tool. We'll design an analysis page that consists of a dynamically updating pivot table. This page will provide valuable insights into your customer relationship data.

Section 1: Creating the New Analysis Worksheet

1. Let's first create a new sheet where the analysis page will be located.

Sub add_sheet()
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Analysis page"
End Sub

Section 2: Creating Pivot Table

Pivot tables enable you to extract significant trends and patterns from large datasets. Here, we'll create a pivot table based on the data input we have.

1. We'll define the range (the whole database) and set it as the Pivot Table's data source.

Sub create_pivotTable()

    Dim PRange As Range
    Dim PSheet As Worksheet
    Dim PTable As PivotTable
    Dim PField As PivotField
    Set PSheet = Sheets("Analysis Page")
    Set PRange = Sheets("Data Input").Range("A1").CurrentRegion

2. Creating the Pivot Table

    Set PTable = PSheet.PivotTableWizard(TableDestination:=PSheet.Cells(1, 1), SourceData:=PRange)
    PTable.TableStyle2 = "PivotStyleLight1"

Section 3: Adding Pivot Fields

1. Adding row fields

    PTable.PivotFields("Contact Name").Orientation = xlRowField
    PTable.PivotFields("Contact Name").Position = 1

2. Adding column fields

    PTable.PivotFields("Product Purchased").Orientation = xlColumnField
    PTable.PivotFields("Product Purchased").Position = 1

3. Adding data fields

    PTable.AddDataField PTable.PivotFields("Purchase Amount"), "Sum of Purchase Amount", xlSum  

The final VBA code within a single routine will look like this:

Sub create_pivotTable()

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Analysis page"
    Dim PRange As Range
    Dim PSheet As Worksheet
    Dim PTable As PivotTable
    Dim PField As PivotField
    Set PSheet = Sheets("Analysis page")
    Set PRange = Sheets("Data Input").Range("A1").CurrentRegion

    Set PTable = PSheet.PivotTableWizard(TableDestination:=PSheet.Cells(1, 1), SourceData:=PRange)
    PTable.TableStyle2 = "PivotStyleLight1"

    PTable.PivotFields("Contact Name").Orientation = xlRowField
    PTable.PivotFields("Contact Name").Position = 1
    
    PTable.PivotFields("Product Purchased").Orientation = xlColumnField
    PTable.PivotFields("Product Purchased").Position = 1

    PTable.AddDataField PTable.PivotFields("Purchase Amount"), "Sum of Purchase Amount", xlSum

End Sub

This will create an Analysis Page in your Excel document and populate it with a detailed pivot table. The pivot table will display the sum of purchase amount, sorted by contact name and product purchased. This analysis can offer valuable insights into purchasing habits and customer value.

Remember to properly secure your VBA scripts and ensure they are triggered correctly within your broader CRM Tool functionality.

Implementing VBA for Data Analysis in CRM Tool

This section will provide a practical guide on how we can use VBA for data analysis in our CRM tool developed using Excel. We'll cover querying data, filtering data, and then some basic but ultimately very useful analytics.

Creating a Query Function

Build a function to pull all necessary from a sheet with the input of "column" and "table name (sheet)". It will come in handy while dealing with a lot of data.

Function query_data(column As Integer, sheet As String) As Variant
    Dim data() As Variant
    Dim lastRow As Long
    lastRow = Worksheets(sheet).Cells(Rows.Count, column).End(xlUp).Row
    data = Worksheets(sheet).Range(Cells(1, column), Cells(lastRow, column))
    query_data = data
End Function

Creating a Filter Function

We're going to create a simple filtering function that accepts the array and a condition, then returns the filtered array.

Function filter_data(array() As Variant, condition As String) As Variant
    Dim results() As String
    Dim p As Integer
    p = 0
    For i = LBound(array) To UBound(array)
        If array(i, 1) = condition Then
            ReDim Preserve results(p)
            results(p) = array(i, 1)
            p = p + 1
        End If
    Next i
    filter_data = results
End Function

Creating a Count Function

Below function counts the length (number of items) in the supplied array.

Function count_data(array() As Variant) As Long
    count_data = UBound(array) - LBound(array) + 1
End Function

Practical Usage Sample

We might use the above functions to analyze our CRM customer data stored in the clients worksheet where column 1 is the client's gender. Let's say, we want to know the number of male and female clients. The below subroutine can be used:

Sub analyze_clients_gender()
    Dim data() As Variant
    data = query_data(1, "clients")

    Dim male_clients() As Variant
    male_clients = filter_data(data, "Male")

    Dim female_clients() As Variant
    female_clients = filter_data(data, "Female")

    Debug.Print "Male clients: " & count_data(male_clients)
    Debug.Print "Female clients: " & count_data(female_clients)
    
End Sub

This code will print the number of male and female clients into the VBA editor's Immediate window.

Keep in mind, the sheet name and column index will depend on your exact data layout and you need to modify accordingly. Please save your workbook as Macro-Enabled Excel Workbook (.xlsm) to store and run the VBA code.

Introduction to Tables and Calculations in Excel VBA

This practical implementation will demonstrate how to work with tables and perform calculations in Excel VBA. Specifically, we will create a script to add a new table, perform some calculations, and update the table dynamically.

Working with Tables in Excel VBA

To begin with, we need to create a worksheet and define the initial range where the data will be placed.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sales")
Dim rng As Range
Set rng = ws.Range("A1:E11")

Now we will create a table in the defined range with the following code:

Dim tbl As ListObject
Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.Name = "SalesTable"
tbl.TableStyle = "TableStyleMedium9"

In the above code, xlSrcRange identifies the source of the data as a range, xlYes indicates that the first row contains headers, "SalesTable" is the name assigned to the table, and "TableStyleMedium9" sets the table’s style.

Performing Calculations in VBA

Now, let's apply some calculations to the data in our table. Assuming we have a sales table with columns "Product", "Price", "Quantity", and "Total". We can automatically calculate total sales.

Dim calcRng As Range
Set calcRng = ws.Range("SalesTable[[#Headers],[Total]]").Offset(1, 0) 'From the 1st row after headers in the "Total" column
calcRng.FormulaR1C1 = "=RC[-1]*RC[-2]"

We use RC[-1] to refer to the cell in the column to the left and RC[-2] to refer to the cell two columns to the left.

Updating the Table Dynamically

Finally, we want our table and calculations to automatically adjust whenever more data is added. To accomplish this, we will use an event procedure in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("SalesTable")) Is Nothing Then 'Check if changes were made in the sales table
       If Target.Column = 2 Or Target.Column = 3 Then 'Check if changes were done in Price or Quantity columns
           Target.Offset(0, 2).FormulaR1C1 = "=RC[-1]*RC[-2]" 'Update "Total" column if Price or Quantity column changed
       End If
   End If
End Sub

This code is added to a specific Worksheet code module. Whenever changes are made in the "Price" or "Quantity" columns of the "SalesTable", it will update the "Total" column accordingly.

This concludes the practical guide on handling tables and calculations in Excel VBA. Remember to adjust your column and row references accordingly, based on your actual data structure.

Note: Always save your work before running any VBA scripts and consider saving your workbook as a Macro-Enabled Workbook (.xlsm file) so that your macros will be preserved.

Automating Calculations using VBA

Scenario

For this practical implementation, let's imagine that we have a CRM Tool where there is a need to calculate the total sales of all products sold by the company. In Excel, this would typically involve summing together certain cells in a column. However, to automate this process, we can create a VBA macro that performs the calculation and saves us the manual steps.

Step 1 – Setting Up a Mock Data Table

Assuming we have an active worksheet that has data on product sales data in three columns: ProductName, UnitsSold, and UnitPrice.

Sub InitializeDataTable()

    Range("A1").Value = "ProductName"
    Range("B1").Value = "UnitsSold"
    Range("C1").Value = "UnitPrice"
    
    Dim Products As Variant
    Products = Array("Product A", "Product B", "Product C", "Product D")
    
    Dim Prices As Variant
    Prices = Array(10, 20, 30, 40)
    
    Dim UnitsSold As Variant
    UnitsSold = Array(200, 250, 100, 150)
    
    For i = 0 To UBound(Products)
        Range("A" & i + 2).Value = Products(i)
        Range("B" & i + 2).Value = UnitsSold(i)
        Range("C" & i + 2).Value = Prices(i)
    Next i

End Sub

This script will create a 4-row data set in columns A, B, and C with a header row. Each row represents a product sold with its respective UnitPrice and UnitsSold.

Step 2 – Creating a VBA Macro for Calculations

We'll create a VBA macro that would automatically calculate the TotalSales. Let's assume the lowest price is in C2 and the lowest UnitsSold is in B2. The calculations would involve multiplying UnitsSold by UnitPrice for all products.

Sub CalculateTotalSales()

    Dim lastRow As Long
    Dim TotalSales As Double
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        TotalSales = TotalSales + Cells(i, "B").Value * Cells(i, "C").Value
    Next i
    
    'Placing the TotalSales at the last row of column D
    Cells(lastRow + 1, "D").Value = "Total Sales:"
    Cells(lastRow + 2, "D").Value = TotalSales

End Sub

This macro will loop over every row in your data table, each time multiplying the value of UnitSold with the respective UnitPrice and adding the result to the TotalSales. In the end, it places the TotalSales value at the last row of column D.

To perform these calculations on a regular basis, you can simply run the 'CalculateTotalSales' macro whenever needed. This auto calculation will help you mechanize your worksheet and is a powerful illustration of how VBA can streamline Excel operations.

***Remember that references to Cells in Excel VBA should always be within the context of a specific Worksheet object. In this example, since our data is in the Active Worksheet, we used 'ActiveSheet.Cells' to perform the calculations.

Conclusion

Given that VBA Macros can interact with Excel's features such as worksheets and cell ranges, you can create more complex macros to perform more advanced mathematical calculations, financial calculations, and statistical analysis, among other things, depending on your need.

Adding Additional Functionalities in Excel VBA

In this section, we will dive into the actual implementation of some additional functionalities in the CRM tool developed using Excel VBA so far in the project. Specifically, we will:

• Add a "Search Customer" feature.
• Add a "Delete Customer" feature.
• Add some data validation to the form.

Search Customer Feature

This feature allows to search customers in the CRM tool using any piece of information (name, contact number, email, or customer id).

The code snippet for the Search feature:

Sub SearchCustomer()
    Dim searchInput As String
    Dim searchRange As Range
    Dim foundCustomer As Range

    ' Get the search input from the user
    searchInput = InputBox("Enter the customer detail to search")
    If searchInput <> "" Then
        Set searchRange = ThisWorkbook.Sheets("CRM Database").Range("A2:E1000")
        ' Search for the customer in the range
        Set foundCustomer = searchRange.Find(What:=searchInput, LookIn:=xlValues, LookAt:=xlPart)
        If Not foundCustomer Is Nothing Then
            MsgBox "Customer Found: " & foundCustomer.Value
        Else
            MsgBox "Customer Not Found!!!"
        End If
    End If
End Sub

Delete Customer Feature

This feature enables to delete any customer's detail from the CRM tool.

The code snippet for the Delete feature:

Sub DeleteCustomer()
    Dim deleteInput As String
    Dim searchRange As Range
    Dim foundCustomer As Range

    ' Get the Delete input from the user
    deleteInput = InputBox("Enter the customer detail to delete")
    If deleteInput <> "" Then
        Set searchRange = ThisWorkbook.Sheets("CRM Database").Range("A2:E1000")
        ' Search for the customer in the range
        Set foundCustomer = searchRange.Find(What:=deleteInput, LookIn:=xlValues, LookAt:=xlPart)
        If Not foundCustomer Is Nothing Then
            foundCustomer.EntireRow.Delete
            MsgBox "Customer Deleted Successfully."
        Else
            MsgBox "Customer Not Found!!!"
        End If
    End If
End Sub

Data Validation

We can add data validation to ensure that the data entered in the input form is valid. For example, we can check that the email address entered is in a valid format.

Add this function to check the validity of an email address:

Function ValidateEmailAddress(email As String) As Boolean
    Dim emailRegex As Object
    Set emailRegex = CreateObject("VBScript.RegExp")
    
    ' Define the regex pattern for the email
    emailRegex.Pattern = "^[\w-]+(\.[\w-]+)*@([\w-]+\.)+[a-zA-Z]{2,7}$"
    
    ' Test the email against the pattern
    If emailRegex.Test(email) Then
        ValidateEmailAddress = True
    Else
        ValidateEmailAddress = False
        MsgBox "Invalid Email Address"
    End If
End Function

Now, use this function before saving the form data to validate the email address. Replace the save code with this:

Sub SaveCustomer()
    Dim rng As Range
    ' Check if email is valid
    If ValidateEmailAddress(txtEmail.Value) Then
        ' Save the form data  
        Set rng = ThisWorkbook.Sheets("CRM Database").Range("A1048576").End(xlUp).Offset(1, 0)
        rng.Value = txtName.Value
        rng.Offset(0, 1).Value = txtContact.Value
        rng.Offset(0, 2).Value = txtEmail.Value
        rng.Offset(0, 3).Value = cboStatus.Value
        rng.Offset(0, 4).Value = txtNotes.Value
        MsgBox "Customer Added Successfully."
    End If
    ClearForm
End Sub

Now, your CRM tool has the ability to search and delete customers, as well as validate email addresses. Please note that code should be added in the appropriate sections of the VBA editor (Modules or UserForms) corresponding to the user interface and functionalities of the CRM Tool for correct operation.

Review and Project Completion in Excel VBA

In this part of the project, we'll work on reviewing and finalizing our CRM tool. We will create a simple routine that checks if all necessary fields are filled out before the form is completed and the necessary records are added to the database.

1. Set up form validation in Excel VBA

Form validation will help ensure data quality by avoiding incomplete or inconsistent data entries. We'll set up a simple form validation routine for the CRM form.

Sub ValidateForm()
    Dim ws As Worksheet
    Set ws = Sheets("FormSheet")
    
    ' Assuming that the form fields are in range A1 to A5
    For i = 1 To 5
        If ws.Range("A" & i).Value = "" Then
            MsgBox "Please fill out all fields before proceeding.", vbExclamation
            ws.Range("A" & i).Select
            Exit Sub
        End If
    Next i
    
    ' If all fields are valid, proceed with form submission
    Call SubmitForm
End Sub

2. Create a routine to submit the form

After validating the form, Excel VBA will submit it and the data should be added to the correct table. An example of how to submit the form after validation could look like this:

Sub SubmitForm()
    Dim wsForm As Worksheet, wsData As Worksheet
    Dim nextRow As Long
    
    Set wsForm = Sheets("FormSheet")
    Set wsData = Sheets("DataSheet")
    
    nextRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Assuming that there are 5 form fields
    For i = 1 To 5
        wsData.Cells(nextRow, i).Value = wsForm.Range("A" & i).Value
    Next i
    
    wsForm.Range("A1:A5").ClearContents
    MsgBox "Form submitted successfully.", vbInformation
End Sub

3. Create a Project Completion Checklist

The Project Completion Checklist will be used to make sure that all the necessary steps have been followed and all the required parts of the project have been completed successfully. Here's an example:

Sub ProjectCompletionChecklist()
    Dim ws As Worksheet
    Set ws = Sheets("ProjectSheet")
    Dim nextRow As Long
    
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Range("A" & nextRow).Value = "Project Completion Checklist"
    ws.Range("A" & nextRow).Font.Bold = True
    
    Dim checklistItems As Variant
    Dim i As Integer
    checklistItems = Array("Introduction to Excel VBA and CRM Tools", _
                           "VBA Programming Fundamentals", _
                           "Designing Data Input Form in Excel VBA", _
                           "Automating Data Input using VBA", _
                           "Creating Analysis Page in Excel", _
                           "Implementing VBA for Data Analysis", _
                           "Introduction to Tables and Calculations in Excel VBA", _
                           "Automating Calculations using VBA", _
                           "Adding Additional Functionalities in Excel VBA", _
                           "Review and Project Completion in Excel VBA")
                           
    nextRow = nextRow + 2
    For i = 0 To UBound(checklistItems)
        ws.Range("A" & nextRow).Value = checklistItems(i)
        ws.Range("B" & nextRow).Value = "COMPLETED"
        nextRow = nextRow + 1
    Next i
End Sub

After running this VBA routine, a completion checklist will be created in the "ProjectSheet" with all the parts of the project marked as "COMPLETED".

Lastly, save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to ensure that your VBA code will be saved with your file.