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
Sub Reset_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 = "" End Sub
VBA code to submit the data
Sub Submit_Details() 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 With shCountry .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") End With shForm.Range("H7, H9, H11, H13, H15").Value = "" MsgBox "Data submitted successfully!" End Sub
Click here to download the Excel file used in this tutorial.