In this post, we will learn how to create a Dynamic Customer Call Log Form in Excel. In call center industry, we need to maintain the call log while taking calls from customers. As we prepare call log, we need to keep those call records in a proper fashion. Later on, we segregate the entries basis on some value e.g. product, country, department, etc. to assign the work to respective department or team for further work.
Let’s assume that we need to separate all the call records by country. After that we need to share the data with respective country SPOCs for further follow-ups and to resolve the issues. In that case, it will be always a good idea to make the entries in separate worksheets while taking calls.
But, handling several sheets will be tough job for an executive while taking call and helping customers. In that case, we can develop a Dynamic Customer Call Log Form in Excel with the help of VBA code. It will help user to enter the data in form while taking call and submit the data. Form will transfer the data to the respective country’s sheet basis on country selection in the Customer Call Log Form.
Here, we can save a lot of time and utilize the segregated data for further issue resolutions, follow-ups and data analysis.
How to create Customer Call Log Form in Excel
Developing this automated and dynamcic form is relatively very easy. You can quickly create this form after watching our YouTube tutorial.
Please watch this tutorial.
VBA code to reset the form
Dim iMessage As VbMsgBoxResult
iMessage = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset Confirmation")
If iMessage = vbNo Then Exit Sub
ThisWorkbook.Sheets("Form").Range("H7,H9,H11,H13,H15").Value = ""
VBA code to submit the data
Dim shCountry As Worksheet
Dim shForm As Worksheet
Dim iCurrentRow As Integer
Dim sCountryName As String
Set shForm = ThisWorkbook.Sheets("Form")
sCountryName = shForm.Range("H11").Value
Set shCountry = ThisWorkbook.Sheets(sCountryName)
iCurrentRow = shCountry.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
.Cells(iCurrentRow, 1) = iCurrentRow - 1
.Cells(iCurrentRow, 2) = shForm.Range("H7")
.Cells(iCurrentRow, 3) = shForm.Range("H9")
.Cells(iCurrentRow, 4) = shForm.Range("H11")
.Cells(iCurrentRow, 5) = shForm.Range("H13")
.Cells(iCurrentRow, 6) = shForm.Range("H15")
.Cells(iCurrentRow, 7) = Application.UserName
.Cells(iCurrentRow, 8) = Format([Now()], "DD-MMM-YYYY HH:MM:SS")
shForm.Range("H7, H9, H11, H13, H15").Value = ""
MsgBox "Data submitted successfully!"
Click here to download the Excel file used in this tutorial.