Automated Complaint Management System

In this post, we are going to learn VBA automation through a live project. We will develop a fully automated Complaint Management System.

We will cover UserForm, Text Box, Label, Combo Box, List Box, Multipage, Command Buttons and other ActiveX controls and respective events in this automated tool. Once you learn all these controls and coding, you will be able to build any data entry application in Excel and VBA.

Let’s start developing the automated complaint management application from scratch.

Creating Excel File for Complaint Management System

Let’s head to start menu and open the Excel application.

Now, create a blank workbook and save the file with “Complaint Management System”. Make sure to select the macro enabled extension i.e. XLSM.

We have saved our file. In this project, we need 4 different worksheets. As one worksheet is already in this workbook, let’s quickly add other three.

To add worksheet, just click on + icon available beside the sheet1.

Now, we have added 4 different sheets required for our project.

Let’s change the name to “Home”, “Pending_Complaints”, “Resolved_Complaints” and “Support_Data”.

Now, time to design the Home sheet and then we will move to other worksheets.

Just select range starting from A1 to X34 and then move to Home Tab and then click on “Merge & Center” under alignment group. To fill the merged range with Gradient fill color, just press the shortcut key CTRL + 1 to open the Format Cells window.

In Format Cells window, Fill tab and then click on Fill Effect.  In Fill effects window, just select ‘Two Colors’ in Gradient group and change the Color 1 to ‘Light Blue’ available in ‘Standard color group’ and Color 2 as ‘White”.

In Shading styles, select the Horizontal option and then select the first variants. Now click on Okay to come out from ‘Fill Effect’ window and again click on Okay button in ‘Format Cells’ window to apply the Gradient colors.

Now you can see that merged ranges are filled with beautiful gradient color.

Let’s create header in this sheet. To do that, let’s head to Inset tab and then under illustration group, click on Shapes and then select the ‘Document’ shape in ‘Flowchart’ section.

Draw this shape for header. Now, fill this shape with Light green color with 70% Transparency.

Now, Right Click on the shape and edit the text to give the Caption as “Complaint Management System”. Change the font size to 60, Font name to ‘Calibri’ and Font color as Black and alignment is ‘Center’.

Now, we have done with header. Let’s insert a shape to create a button with name “Raise Complaint”.

Apply the required formatting and shadow.

We have done with Home sheet. Let’s create required Headers in “Pending_Complaints” sheet.

Columns are : Date, Employee Name, Customer Name, Customer Email, Customer Country, Reason for Complaint, Case Priority, VIP Customer? Complaint Description

Now, we have done with ‘Pending_Complaint’ sheet. Let us create the headers in ‘Resolved_Complaints’.

Columns are : Date, Employee Name, Customer Name, Customer Email, Customer Country, Reason for Complaint, Case Priority, VIP Customer? Complaint Description, Resolution Summary, Case Handled By, Case Handled On

Let’s move to ‘Support_Data’ sheet and create two different list for Country and Reasons. We will be utilizing three combo box in our project so that user can select Country Name, Reason and Priority for complaints. To make first two combo box dynamic, we have to create Country and Reasons list in Support_Data sheet.

Mention all the countries in country name and reason in reason name list. Please refer the below image.

So, we have done with all the required worksheets and data to develop this automated tracker. Let’s move to ‘Visual Basic for Application’ window and develop the tracker.

Designing UserForms and Inserting Module

To jump to VBA window, click on ‘Developer Tab and then under ‘Code’ Group, click on VBA button. Alternatively, you can also press shortcut key as ALT + F11 to open VBA window.

Now, we are in Visual Basic for Application window. Here, we need to insert two different User Forms (one is to raise the complaint and second one is to resolve the complaint) and one module to contain most of the Sub procedures and function.

To insert a form, head to Insert menu and then click on ‘User Form’. Repeat the same process to insert one more form.

Let’s add a blank module. To do that, just click on ‘Insert’ menu and then select ‘Module’.

Now, you can see that we have two different forms and one module in our project.

Let’s design the User Interface in both the forms and then we will start writing the code.

Designing ‘Complaint Management System’ main form

Let us set the properties of Complaint Management System form and add required controls.

As form will have three different parts (First is to raise the compliant, second is to show the summary and third is to show the list of Pending and Resolved complaints), let us design the form.

Please see the below image and insert the controls and set the properties accordingly (You can watch the YouTube tutorial for all the required steps)

Now, we have done with designing the Complaint Management main form. Let’s set the tab order.

Designing Resolve Complaint Form

Let’s design the next form which is for resolving the pending complaints. See the below image and replicate the design and properties.

Now set the tab order for all the input controls.

Here, we have done with designing both the forms. Let us start writing the code.

Writing VBA Code to Raise and Resolve a Complaint

Now, let us start writing the functions and sub procedures required for our project in module.

So, first of all we will write a Sub Procedure to apply default color white as a background color to Textbox and Combo box input controls. With the help of this module, we will be able to remove the red background color after passing the input validation. 

Sub DefaultColor_Pending()

    With frmComplaint
            
        'Set the Default White color
        
        .txtDate.BackColor = vbWhite
        .txtEmpName.BackColor = vbWhite
        .txtCustName.BackColor = vbWhite
        .txtEmail.BackColor = vbWhite
        .cmbCountry.BackColor = vbWhite
        .cmbReason.BackColor = vbWhite
        .cmbPriority.BackColor = vbWhite
        .txtDescription.BackColor = vbWhite
        
    End With

End Sub

Now, let us create a new sub procedure to assign the row source reference to the Pending and Resolved Listbox controls. RowSource will be fully dynamic hence, code will auto adjust the ranges of listbox basis the data availability in the respective worksheets.

Sub ListBoxReference()
        
        'Assign the reference to listbox - Pending and Resolved
        
        Dim iRow_Pending As Long 'Last Blank row in Pending Complaints
        Dim iRow_Resolved  As Long 'Last blank row in resolved complaints
    
        iRow_Pending = ThisWorkbook.Sheets("Pending_Complaints").Range("A" & Application.Rows.Count).End(xlUp).Row
        iRow_Resolved = ThisWorkbook.Sheets("Resolved_Complaints").Range("A" & Application.Rows.Count).End(xlUp).Row
    
        'Setting the Reference to ListBox - Pending Complaints
        
        frmComplaint.lstPendingComplaints.ColumnCount = 9
        frmComplaint.lstPendingComplaints.ColumnHeads = True
        
        If iRow_Pending = 1 Then
            
           frmComplaint.lstPendingComplaints.RowSource = "Pending_Complaints!A2:I2"
            
        Else
        
            frmComplaint.lstPendingComplaints.RowSource = "Pending_Complaints!A2:I" & iRow_Pending
        
        End If
        
        
         'Setting the Reference to ListBox - Resolved Complaints
        frmComplaint.lstResolvedComplaints.ColumnCount = 12
        frmComplaint.lstResolvedComplaints.ColumnHeads = True
        
        If iRow_Resolved = 1 Then
            
           frmComplaint.lstResolvedComplaints.RowSource = "'Resolved_Complaints'!A2:L2"
            
        Else
        
            frmComplaint.lstResolvedComplaints.RowSource = "'Resolved_Complaints'!A2:L" & iRow_Resolved
        
        End If
     

End Sub

Now, we have done with dynamic list range to country and reason combo box.

Let us write a new sub procedure to initialize all the controls available in Complaint Management System form. With the help of this procedure, we will reset the form, refresh the source data of listbox and combo box.

Sub Initialize_PendingComplaint()

    With frmComplaint
            
        'Set the default Value
        
        .txtDate.Value = [Text(Today(), "DD-MMM-YYYY")]
        .txtDate.Enabled = False
        
        .txtEmpName.Value = Environ("username")
        .txtEmpName.Enabled = False
        
        .txtCustName.Value = ""
        .txtEmail.Value = ""
        
                
        'Creating a dynamic name for Country and assigning it to Country combo-box
        
        Sheets("Support_Data").Range("A2", Sheets("Support_Data").Range("A" & Application.Rows.Count).End(xlUp)).Name = "Country"
        
        .cmbCountry.RowSource = "Country"
        
        .cmbCountry.Value = ""
        
        
        'Creating a dynamic name for Reason and assigning it to Reason combo-box
        
        Sheets("Support_Data").Range("C2", Sheets("Support_Data").Range("C" & Application.Rows.Count).End(xlUp)).Name = "Reason"
        
        .cmbReason.RowSource = "Reason"
        
        .cmbReason.Value = ""
        
        'Priority Combo-box
        
        .cmbPriority.Clear
        .cmbPriority.AddItem "High"
        .cmbPriority.AddItem "Medium"
        .cmbPriority.AddItem "Low"
        
        .cmbPriority.Value = ""
        
        'VIP customer
        .chkVIP.Value = False
        
        'Complaint Description
        .txtDescription.Value = ""
        
         'Activate the Pending Page in MultiPage control
         
         'Selecting 1 then 2 page in multipage so that event on page selection will get fired and calculate the numbers
         .MultiPage.Value = 1 ' one for 1st page i.e. Resolved
         .MultiPage.Value = 0 ' zero for 1st page i.e. pending
    
    End With
   
    'Setting the reference of Listbox
    
    Call ListBoxReference
    
    'Assigning the default colors to input controls
    
    Call DefaultColor_Pending

End Sub

Let’s write a Function to validate the entries made by user. Function type would be Boolean; it means if any of the input will be invalid as per validation then it will return FALSE otherwise it will be TRUE.

'Function To Validate the Entries

Function ValidatePending() As Boolean
    
    ValidatePending = True
    
    Call DefaultColor_Pending   ' Call Default Color procedure to apply white color as a back color to inputs
    
    With frmComplaint
    
        If .txtCustName.Value = "" Then
        
            MsgBox "Please enter the customer name.", vbOKOnly + vbInformation, "Customer Name"
            .txtCustName.SetFocus
            .txtCustName.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        ElseIf .txtEmail.Value = "" Then
            
             MsgBox "Please enter the email id.", vbOKOnly + vbInformation, "Email ID"
            .txtEmail.SetFocus
            .txtEmail.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        ElseIf .cmbCountry.Value = "" Then
        
             MsgBox "Please select the Country name from the drop-down.", vbOKOnly + vbInformation, "Country Name"
            .cmbCountry.SetFocus
            .cmbCountry.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        ElseIf .cmbReason.Value = "" Then
        
             MsgBox "Please select the Complaint reason from the drop-down.", vbOKOnly + vbInformation, "Complaint Reason"
            .cmbReason.SetFocus
            .cmbReason.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        ElseIf .cmbPriority.Value = "" Then
        
             MsgBox "Please select the priority from the drop-down.", vbOKOnly + vbInformation, "Priority"
            .cmbPriority.SetFocus
            .cmbPriority.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        ElseIf .txtDescription.Value = "" Then
        
            MsgBox "Please enter the description.", vbOKOnly + vbInformation, "Description"
            .txtDescription.SetFocus
            .txtDescription.BackColor = vbRed
            ValidatePending = False
            Exit Function
            
        End If
        
    End With

End Function

Let’s create a new sub procedure to show the form. We will utilize this procedure while assigning the macro on ‘Raise Complaint’ button available in Excel sheet.

Sub Show_Form()

    frmComplaint.Show

End Sub

So, we have done with the required code for Module 1.
Let’s move to Complaint Management System form and start writing the code and calling the procedures & functions while handling the events.

So, first of all we will write the code on Form Initialization event. Let us select the Initialize the event from the dropdown.

As we have already written a sub procedure to handle initialize or reset process. Let’s call that procedure here.

Private Sub UserForm_Initialize()

    Call Initialize_PendingComplaint
    
End Sub

Let us write the code to update the numbers in summary section of this form. Here, we will update the numbers on page change event of MultiPage control.

Let us select MultiPage from the object drop-down and change event from the events drop-down.

Write the below code to update the summary basis data availability in Pending and Resolve sheets.

Private Sub MultiPage_Change()
        
    Dim i As Integer, j As Integer, k As Integer  'To store the count High, Medium and Low priority cases
        
    If MultiPage.Value = 0 Then ' zero for Pending Tab
    
        i = [COUNTIF(Pending_Complaints!G:G, "High")]
        
        j = [COUNTIF(Pending_Complaints!G:G, "Medium")]
        
        k = [COUNTIF(Pending_Complaints!G:G, "Low")]
        
        With frmComplaint
        
            .frmSummary.Caption = "Pending Complaints Summary"
            
            .lblHigh.Caption = i
            .lblMedium.Caption = j
            .lblLow.Caption = k
            .lblOverall.Caption = i + j + k
        
        End With
    Else
    
        i = [COUNTIF(Resolved_Complaints!G:G, "High")]
        
        j = [COUNTIF(Resolved_Complaints!G:G, "Medium")]
        
        k = [COUNTIF(Resolved_Complaints!G:G, "Low")]
        
        With frmComplaint
        
            .frmSummary.Caption = "Resolved Complaints Summary"
            
            .lblHigh.Caption = i
            .lblMedium.Caption = j
            .lblLow.Caption = k
            .lblOverall.Caption = i + j + k
        
        End With
    End If
 End Sub

Let us write the code on click event of Reset Button.

Private Sub cmdReset_Click()
    
    Dim i As VbMsgBoxResult
    
    i = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Reset")
    
    If i = vbNo Then Exit Sub
    
    Call Initialize_PendingComplaint
    
End Sub

Now, we need to write the code to transfer the inputs to Pending_Complaints sheet and show the data to Pending Complaints listbox.

Let us write the code on Click event of Submit button.

Private Sub cmdSubmit_Click()
    
    
    Dim msgResult As VbMsgBoxResult
    
    msgResult = MsgBox("Do you want to tranfer the data?", vbYesNo + vbQuestion, "Transfer")
       
    If msgResult = vbNo Then Exit Sub
       
    'Validation entries
    If ValidatePending = False Then Exit Sub
    
    Dim sh As Worksheet
    Dim iRow As Long ' Variable to store the last blank row
    
    Set sh = ThisWorkbook.Sheets("Pending_Complaints")
    
    
    iRow = sh.Range("A" & Rows.Count).End(xlUp).Row + 1 ' Last blank row
    
    With frmComplaint
    
        sh.Range("A" & iRow).Value = .txtDate.Value                 'Date
        sh.Range("B" & iRow).Value = .txtEmpName.Value              'Employee Name
        sh.Range("C" & iRow).Value = .txtCustName.Value             'Customer Name
        sh.Range("D" & iRow).Value = .txtEmail.Value                'Email ID
        sh.Range("E" & iRow).Value = .cmbCountry.Value              'Country
        sh.Range("F" & iRow).Value = .cmbReason.Value               'Reason
        sh.Range("G" & iRow).Value = .cmbPriority.Value             'Priority
        sh.Range("H" & iRow).Value = IIf(.chkVIP.Value, "Yes", "No") 'VIP Customer
        sh.Range("I" & iRow).Value = .txtDescription.Value          'Description
    
    End With
    
    'Reset the form after transferring the data
    
    Call Initialize_PendingComplaint
    
End Sub

Now, we need to write the code to open the frmResolve form with selected data. So, here whenever user will double click on any of the available records in Pending Complaints listbox then it will open the Resolve complaint form with selected data.

Let us double click on lstPendingComplaints listbox and write the code on double click event.

Private Sub lstPendingComplaints_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 
    'If no data available in lstPendingComplaints
    If Me.lstPendingComplaints.List(Me.lstPendingComplaints.ListIndex, 0) = "" Then Exit Sub
    
    
    'Code to update the value to respective controls
    
    Load frmResolve ' Load the form in memory
    
    'Code to load the default values to frmResolve
    
    With frmResolve
    
        .txtDate.Value = [Text(Today(), "DD-MMM-YYYY")]
        .txtDate.Enabled = False
        
        .txtEmpName.Value = Environ("username")
        .txtEmpName.Enabled = False
        
        .txtCustName.Value = Me.lstPendingComplaints.List(Me.lstPendingComplaints.ListIndex, 2) ' Customer Name - 3rd Column
        .txtCustName.Enabled = False
        
        .txtCustEmail.Value = Me.lstPendingComplaints.List(Me.lstPendingComplaints.ListIndex, 3) ' Customer Email ID - 4th Column
        .txtCustEmail.Enabled = False
        
        .txtSummary.Value = ""
        
        .Show 'Show the form
    
    End With
    
End Sub

Here, we have done with all the codes required on first form i.e. Complaint Management system.

Let us move to the next form i.e. Resolve Complaint and write the code to submit the data to Resolve Complaint worksheet and delete the respective record from Pending Complaints data.

Let’s double click on Submit button on Resolve Complaint form and write the below code.

Private Sub cmdSubmit_Click()

    
    'Code to take the confirmation from user
    
    Dim msgResult As VbMsgBoxResult
    
    msgResult = MsgBox("Do you want to submit this data?", vbYesNo + vbQuestion, "Submit Resolution")
    
    If msgResult = vbNo Then Exit Sub
    
    'Validating the Resolution summary
    
    If Me.txtSummary.Value = "" Then

        Me.txtSummary.BackColor = vbRed
        Me.txtSummary.SetFocus

        MsgBox "Resolution summary is blank.", vbOKOnly + vbInformation, "Summary"
        Exit Sub

    End If
    
    Dim iRow As Long 'Variable to identify the next blank row in Resolved Complaints sheet
    Dim sh As Worksheet ' variable to refer the resolved complaint sheet
    Dim lstRow As Long 'Variable to store the selected row in list box
    
       
    Me.txtSummary.BackColor = vbWhite 'Default color
    
    Set sh = ThisWorkbook.Sheets("Resolved_Complaints") 'Setting the reference of Resolved Complaints sheet
    
    iRow = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 'Identifying the last blank row
    
    
    'Transferring data to Resolved_Complaints sheet
    
    With frmComplaint.lstPendingComplaints
    
        lstRow = .ListIndex 'store the selected row number in pending complaint list
    
        sh.Range("A" & iRow).Value = Format(.List(lstRow, 0), "dd-mmm-yyyy") 'Date
        
        sh.Range("B" & iRow).Value = .List(lstRow, 1) 'Complaint Raised by - Emp Name
        sh.Range("C" & iRow).Value = .List(lstRow, 2) ' Customer Name
        sh.Range("D" & iRow).Value = .List(lstRow, 3) 'Customer email
        sh.Range("E" & iRow).Value = .List(lstRow, 4) 'Country
        sh.Range("F" & iRow).Value = .List(lstRow, 5) 'Reason
        sh.Range("G" & iRow).Value = .List(lstRow, 6) 'Priority
        sh.Range("H" & iRow).Value = .List(lstRow, 7) 'VIP Customer
        sh.Range("I" & iRow).Value = .List(lstRow, 8) 'Complaint Description
        
        sh.Range("J" & iRow).Value = frmResolve.txtSummary.Value 'Resolution Description
        sh.Range("K" & iRow).Value = frmResolve.txtEmpName.Value 'Commplaint Handled by
        sh.Range("L" & iRow).Value = frmResolve.txtDate.Value 'Case Handled Date
    
    End With
    
        
    Sheets("Pending_Complaints").Rows(lstRow + 2).EntireRow.Delete 'for row 1, lstRow will show 0 hence we need to add 2 as 1st row in sheet is for header
    
    Call ListBoxReference   'Refreshing the listbox range to show the udpated data
    
    Unload Me 'Closing the resolution form

End Sub

So we have done with all the codes. Let us head to Debug menu and compile our VBA project to find any syntax error. If there would be no error then you can move to Excel window and assign the macro on button available in Home sheet.

So this is all about the Complaint management system in Excel.

I hope you have enjoyed this lesson and if you have any question you can always ask me in comment section.

Please watch the YouTube Tutorial.

CMS Part 1 (Demo)

CMS Part 2 (Creating Excel file and Support Data)

CMS Part 3 (Creating UserForm and Input Controls)

CMS Part 4 (Creating Resolve Complaints UserForm)

CMS Part 5 (Writing Sub Procedures and Functions)

CMS Part 6 (VBA Code to resolve the pending complaints)

Please download the practice from the below link.