Automated Complaint Management System

Complaint Management System in Excel
Complaint Management System in Excel

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.

VBA Toolbox

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.

Creating Excel File

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

Renaming sheets

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

Designing Home Sheet

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.

Inserting button

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

Pending Complaint Sheet

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.

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

Inserting Form and 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. 

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here