What is Call Center Transaction Monitoring?
Call Center Transaction Monitoring, also known as call audit, is an ongoing process of closely monitoring of some of the random inbound/outbound calls and sharing feedback with agents basis on audit result. It helps agents in following the best practices and identifying the areas of opportunities. Call audits are done by Quality Analyst (QA) or Team Leader (TL) using call center audit forms. It helps company in increasing productivity and quality by identifying the area of improvement and providing required coaching to the employees.
Why Excel based monitoring form?
Microsoft Excel is a platform which allows us to develop a fully automated and customized template, tools and reports for our business requirement. We don’t need to get an additional license for that. Also, we don’t need to invest in acquiring some additional tools for monitoring. In call center, almost all employees have MS Excel installed at their machine and this is the added advantage. We can simply download the Monitoring Template and start using after making some adjustment, if required.
About Call Center Quality Audit Form provided by TheDataLabs
TheDataLabs team has developed a completely free and downloadable Call Center Transaction Monitoring form in Excel & VBA. It has all the features e.g. Monitoring Form, Dashboard, Auto Email for Feedback and Audit Database.
Our team has tried to developed this form in such a way that it can be easy for any of the companies to start using the template in day to day call monitoring, creating repository of all the audits and sending feedback to agents with auto-feedback facility.
Understanding the Audit Form, Structure and VBA Code
Audit Form
Audit form has five different sections: 1. Basic Information, 2. Buttons and Controls, 3. Audit Score Summary, 4. Audit Details (Parameters, Sub-Parameters and Audit Results) and 5. Overall Feedback
The first section, ‘Employee & Other Details‘, is for basic information updated by QA/TL while auditing the calls. It has all the basic fields which are required for a call audit and providing feedback. In this section, we have fields like ‘Employee ID’, ‘Employee Name’, ‘Employee Email ID’, ‘Client Name’, ‘Call Date’, ‘Transaction ID’, ‘Auditor’s Name’, ‘Audit Date’ and ‘Feedback Shared’. All of the fields are free text input except ‘Feedback Shared’. ‘Feedback Shared’ field is a drop-down field with value ‘Yes’ and ‘No’. Auditor’s Name and Audit Date are auto populated fields but both can be changed by auditor, if required.
The second section has all the controls to submit the call monitoring details to Audit Dump sheet, creating an email for feedback and resetting the entire form.
The third section provides the category-wise and overall scores. It generates real-time summary and calculates score basis the audit inputs provided by auditors.
The fourth section is ‘Audit Details’. It has all the parameters and sub-parameters on which auditor will perform the call audits. This template has Categories (Greeting, Courtesy & Communication, Resolution, Documentation and Process Requirement) and Sub-Categories (as mentioned in below image) along with Weightage, Audit Result (input fields) and Comments (input fields).
The fifth section is for overall feedback. Auditor can update the overall feedback, opportunity areas and other remarks. It will be saved in ‘Audit Dump’ sheet so that anyone can refer the data in future or to prepare report, if required.
Structure of Audit Form
In this audit form, we have three different Worksheets. 1. Form, 2. Audit Dump and 3. Email Summary.
Form sheet has all the input fields, buttons and others to perform the audits and share the feedback.
Audit Dump sheet is to maintain the repository of all the audits done by auditor. Once, user will click on ‘Save’ button to transfer the audit data and feedback, VBA code will update the details below the respective columns in ‘Audit Dump’ sheet.
The third sheet, Email Summary has been used for internal purpose only. VBA utilized this sheet to auto-populate all the required data and then draft an email and embed this sheet data as picture to send the audit feedback to respective agent.
VBA Code used in the Call Center Quality Audit Form
In this project, we have used three different modules and some event based VBA procedure to perform all the required actions.
Feedback Email Code – Below code has been used to draft an email to send the feedback to agent along with all the required summary in mail body.
Sub Feedback_Email()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo err_msg
Dim OutApp As Object
Dim OutMail As Object
Dim shForm As Worksheet
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set shForm = ThisWorkbook.Sheets("Form")
With OutMail
.To = shForm.Range("D11").Value
.cc = "info@thedatalabs.org"
.Subject = "Call Audit Feedback - " & shForm.Range("D11").Value
.HTMLBody = "<P> Dear " & shForm.Range("D10").Value & ",<br><br>Below is the audit summary. <br>" & RangetoHTML(ThisWorkbook.Sheets("Email Summary").Range("A1:M49")) & "<br>Regards,<br>" & shForm.Range("L9").Value & "</P>"
.display
'.Send ' remove comment if you want to send the email without reviewing it
End With
Exit Sub
err_msg:
Load frm_Error
frm_Error.Label1.ControlTipText = "Error Number:-" & Err.Number & vbLf & vbLf & " || Error Detail:- " & Err.Description & " || "
frm_Error.Show False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End SubVBA code to convert Audit details to HTML
'VBA Function to convert given range to HTML
Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & VBA.Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = VBA.Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
TempWB.Close savechanges:=False
VBA.Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End FunctionVBA Code to Initialize or Reset the Form
' Procedure to initialize the Cells, Controls
Sub InitializeSheet()
Application.ScreenUpdating = False
Dim shForm As Object
Set shForm = ThisWorkbook.Sheets("Form")
shForm.Activate
shForm.Unprotect Password:="thedatalabs"
'Employee & Other Details
shForm.Range("D9:D11").Value = ""
shForm.Range("H9:H11").Value = ""
shForm.Range("L9:L11").Value = ""
'Audit Score & Comments
shForm.Range("J32:J34").Value = ""
shForm.Range("L32:L34").Value = ""
shForm.Range("J38:J42").Value = ""
shForm.Range("L38:L42").Value = ""
shForm.Range("J46:J50").Value = ""
shForm.Range("L46:L50").Value = ""
shForm.Range("J54:J55").Value = ""
shForm.Range("L54:L55").Value = ""
shForm.Range("J59:J60").Value = ""
shForm.Range("L59:L60").Value = ""
' Feedbak/Remarks
shForm.Range("B64").Value = ""
'Adding Validation to feedback Shared Yes/No
shForm.Range("L11").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No"
End With
'Adding Validation
'Greeting & Closing Script
shForm.Range("J32").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J33").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J34").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
'Courtesy & Communication
shForm.Range("J38").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J39").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J40").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J41").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J42").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
'Issue Resolution
shForm.Range("J46").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J47").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J48").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J49").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J50").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
'Case Documentation
shForm.Range("J54").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J55").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
'Process Compliance
shForm.Range("J59").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("J60").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No,N/A"
End With
shForm.Range("L9").Value = Application.UserName
shForm.Range("L10").Value = [Today()]
shForm.optYes = False
shForm.optNo = False
shForm.Activate
shForm.Range("D9").Select
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollColumn = 1
shForm.Protect Password:="thedatalabs"
Application.ScreenUpdating = True
End SubVBA Function to take the confirmation before resetting the form
'Procedure to Reset the form
Sub Reset()
Dim iMsg As Integer
iMsg = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset Confirmation")
If iMsg = vbYes Then
Call InitializeSheet
End If
End SubVBA function to Validate the Audit Form entries
'Function to Validate the Entry
Function CheckEntry() As Boolean
Dim shForm As Object
Set shForm = ThisWorkbook.Sheets("Form")
CheckEntry = True
'1st Section
If Trim(shForm.Range("D9").Value) = "" Then
MsgBox "Employee ID can't be blank.", vbOKOnly + vbInformation, "Employee ID"
Range("D9").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("D10").Value) = "" Then
MsgBox "Employee Name can't be blank.", vbOKOnly + vbInformation, "Employee Name"
Range("D10").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("D11").Value) = "" Then
MsgBox "Employee Email ID can't be blank.", vbOKOnly + vbInformation, "Email ID"
Range("D11").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("H9").Value) = "" Then
MsgBox "Client Name can't be blank.", vbOKOnly + vbInformation, "Client Name"
Range("H9").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("H10").Value) = "" Then
MsgBox "Call Date can't be blank.", vbOKOnly + vbInformation, "Call Date"
Range("H10").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("H11").Value) = "" Then
MsgBox "Transaction ID can't be blank.", vbOKOnly + vbInformation, "Transaction ID"
Range("H11").Select
CheckEntry = False
Exit Function
End If
'3rd Section
If Trim(shForm.Range("L9").Value) = "" Then
MsgBox "Auditor's Name can't be blank.", vbOKOnly + vbInformation, "Auditor's Name"
Range("L9").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("L10").Value) = "" Then
MsgBox "Audit Date can't be blank.", vbOKOnly + vbInformation, "Audit Date"
Range("L10").Select
CheckEntry = False
Exit Function
End If
If Trim(shForm.Range("L11").Value) = "" Then
MsgBox "Please select 'Feedback Shared' Yes or No from the drop-down.", vbOKOnly + vbInformation, "Feedback Shared"
Range("L11").Select
CheckEntry = False
Exit Function
End If
'Category Validatin
'Greeting & Closing Script - 1
'Did the agent say thank you for calling or apply a local greeting?
If Trim(shForm.Range("J32").Value) = "" Or (Trim(shForm.Range("J32").Value) <> "Yes" _
And Trim(shForm.Range("J32").Value) <> "No" And Trim(shForm.Range("J32").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 1 "
Range("J32").Select
CheckEntry = False
Exit Function
End If
'Did agent offer further assistance before closing the call ?
If Trim(shForm.Range("J33").Value) = "" Or (Trim(shForm.Range("J33").Value) <> "Yes" _
And Trim(shForm.Range("J33").Value) <> "No" And Trim(shForm.Range("J33").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 2 "
Range("J33").Select
CheckEntry = False
Exit Function
End If
'Did the agent follow appropriate closing procedure ?
If Trim(shForm.Range("J34").Value) = "" Or (Trim(shForm.Range("J34").Value) <> "Yes" _
And Trim(shForm.Range("J34").Value) <> "No" And Trim(shForm.Range("J34").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Greeting - Parameter 3 "
Range("J34").Select
CheckEntry = False
Exit Function
End If
'Courtesy & Communication -2
'Did agent sound polite and courteous on the call ?
If Trim(shForm.Range("J38").Value) = "" Or (Trim(shForm.Range("J38").Value) <> "Yes" _
And Trim(shForm.Range("J38").Value) <> "No" And Trim(shForm.Range("J38").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 1 "
Range("J38").Select
CheckEntry = False
Exit Function
End If
'Did agent empathize with customer's issue ?
If Trim(shForm.Range("J39").Value) = "" Or (Trim(shForm.Range("J39").Value) <> "Yes" _
And Trim(shForm.Range("J39").Value) <> "No" And Trim(shForm.Range("J39").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 2 "
Range("J39").Select
CheckEntry = False
Exit Function
End If
'Did agent modulate his pitch and volume according to customer's ?
If Trim(shForm.Range("J40").Value) = "" Or (Trim(shForm.Range("J40").Value) <> "Yes" _
And Trim(shForm.Range("J40").Value) <> "No" And Trim(shForm.Range("J40").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 3 "
Range("J40").Select
CheckEntry = False
Exit Function
End If
'Dead air for less than 15 secs
If Trim(shForm.Range("J41").Value) = "" Or (Trim(shForm.Range("J41").Value) <> "Yes" _
And Trim(shForm.Range("J41").Value) <> "No" And Trim(shForm.Range("J41").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 4 "
Range("J41").Select
CheckEntry = False
Exit Function
End If
'Did the agent follow the correct Hold procedure ?
If Trim(shForm.Range("J42").Value) = "" Or (Trim(shForm.Range("J42").Value) <> "Yes" _
And Trim(shForm.Range("J42").Value) <> "No" And Trim(shForm.Range("J42").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Courtesy & Communication - Parameter 5 "
Range("J42").Select
CheckEntry = False
Exit Function
End If
'Issue Resolution - 3
'Did the agent authenticate customer and confirm the issue?
If Trim(shForm.Range("J46").Value) = "" Or (Trim(shForm.Range("J46").Value) <> "Yes" _
And Trim(shForm.Range("J46").Value) <> "No" And Trim(shForm.Range("J46").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 1"
Range("J46").Select
CheckEntry = False
Exit Function
End If
'Did the agent do effective probing on the call?
If Trim(shForm.Range("J47").Value) = "" Or (Trim(shForm.Range("J47").Value) <> "Yes" _
And Trim(shForm.Range("J47").Value) <> "No" And Trim(shForm.Range("J47").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 2"
Range("J47").Select
CheckEntry = False
Exit Function
End If
'Did the agent provide accurate and complete resolution on the call?
If Trim(shForm.Range("J48").Value) = "" Or (Trim(shForm.Range("J48").Value) <> "Yes" _
And Trim(shForm.Range("J48").Value) <> "No" And Trim(shForm.Range("J48").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 3"
Range("J48").Select
CheckEntry = False
Exit Function
End If
'Did the agent provide clear understanding of the issue to the customer?
If Trim(shForm.Range("J49").Value) = "" Or (Trim(shForm.Range("J49").Value) <> "Yes" _
And Trim(shForm.Range("J49").Value) <> "No" And Trim(shForm.Range("J49").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 4"
Range("J49").Select
CheckEntry = False
Exit Function
End If
'Did the agent process the request as promised to the customer?
If Trim(shForm.Range("J50").Value) = "" Or (Trim(shForm.Range("J50").Value) <> "Yes" _
And Trim(shForm.Range("J50").Value) <> "No" And Trim(shForm.Range("J50").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Resolution - Parameter 5"
Range("J50").Select
CheckEntry = False
Exit Function
End If
'Case Documentation - 4
'Agent documented the case correctly
If Trim(shForm.Range("J54").Value) = "" Or (Trim(shForm.Range("J54").Value) <> "Yes" _
And Trim(shForm.Range("J54").Value) <> "No" And Trim(shForm.Range("J54").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Documentation - Parameter 1"
Range("J54").Select
CheckEntry = False
Exit Function
End If
'Agent escalated the case to the right department with all relevant details
If Trim(shForm.Range("J55").Value) = "" Or (Trim(shForm.Range("J55").Value) <> "Yes" _
And Trim(shForm.Range("J55").Value) <> "No" And Trim(shForm.Range("J55").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Documentation - Parameter 2"
Range("J55").Select
CheckEntry = False
Exit Function
End If
'Process Compliance - 5
'Did agent offer discount against SOP without prior approval from TL?
If Trim(shForm.Range("J59").Value) = "" Or (Trim(shForm.Range("J59").Value) <> "Yes" _
And Trim(shForm.Range("J59").Value) <> "No" And Trim(shForm.Range("J59").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Process Compliance - Parameter 1 "
Range("J59").Select
CheckEntry = False
Exit Function
End If
'Did the agent modified case fields to avoid survey going to the customer?
If Trim(shForm.Range("J60").Value) = "" Or (Trim(shForm.Range("J60").Value) <> "Yes" _
And Trim(shForm.Range("J60").Value) <> "No" And Trim(shForm.Range("J60").Value) <> "N/A") Then
MsgBox "Audit Result can't be Blank/Invalid. Please select from Drop Down.", vbOKOnly + vbInformation, "Process Compliance - Parameter 2 "
Range("J60").Select
CheckEntry = False
Exit Function
End If
' Feedback Shared
If shForm.optYes.Value = False And shForm.optNo.Value = False Then
MsgBox "Please select 'Send Feedback Mail' Option (Yes/No).", vbOKOnly + vbInformation, "Send Feedback"
Range("B13").Select
CheckEntry = False
Exit Function
End If
End FunctionVBA Function to Transfer the Audit data for Audit Dump
Sub Transfer()
Dim iRow As Integer
Dim shForm As Object
Dim shDump As Object
Set shForm = ThisWorkbook.Sheets("Form")
Set shDump = ThisWorkbook.Sheets("Audit Dump")
shDump.Select
iRow = shDump.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 'Identify the last row
With shDump
''Employee & Other Details
.Cells(iRow, 1).Value = shForm.Range("D9").Value 'Employee ID
.Cells(iRow, 2).Value = shForm.Range("D10").Value ' Employee Name
.Cells(iRow, 3).Value = shForm.Range("D11").Value 'Employee Email ID
.Cells(iRow, 4).Value = shForm.Range("H9").Value 'Client Name
.Cells(iRow, 5).Value = shForm.Range("H10").Value 'Call Date
.Cells(iRow, 6).Value = shForm.Range("H11").Value 'Transaction ID
.Cells(iRow, 7).Value = shForm.Range("L9").Value 'Auditor's Name
.Cells(iRow, 8).Value = shForm.Range("L10").Value 'Audit Date
.Cells(iRow, 9).Value = shForm.Range("L11").Value 'Feedback Shared
' Audit Score Summary
.Cells(iRow, 10).Value = shForm.Range("F24").Value 'Overall Appliable Points
.Cells(iRow, 11).Value = shForm.Range("H24").Value 'Overall Earned Points
.Cells(iRow, 12).Value = shForm.Range("J24").Value 'Overall Score
'Greeting -1
.Cells(iRow, 13).Value = shForm.Range("J32").Value 'Did the agent say thank you for calling or apply a local greeting? - Audit Result
.Cells(iRow, 14).Value = shForm.Range("N32").Value 'Did the agent say thank you for calling or apply a local greeting? - Applicable Points
.Cells(iRow, 15).Value = shForm.Range("O32").Value 'Did the agent say thank you for calling or apply a local greeting? - Earned Points
.Cells(iRow, 16).Value = shForm.Range("L32").Value 'Did the agent say thank you for calling or apply a local greeting? - Comment
.Cells(iRow, 17).Value = shForm.Range("J33").Value 'Did agent offer further assistance before disconnecting the chat? - Audit Result
.Cells(iRow, 18).Value = shForm.Range("N33").Value 'Did agent offer further assistance before disconnecting the chat? - Applicable Points
.Cells(iRow, 19).Value = shForm.Range("O33").Value 'Did agent offer further assistance before disconnecting the chat? - Earned Points
.Cells(iRow, 20).Value = shForm.Range("L33").Value 'Did agent offer further assistance before disconnecting the chat? - Comment
.Cells(iRow, 21).Value = shForm.Range("J34").Value 'Did the agent follow appropriate closing procedure? - Audit Result
.Cells(iRow, 22).Value = shForm.Range("N34").Value 'Did the agent follow appropriate closing procedure? - Applicable Points
.Cells(iRow, 23).Value = shForm.Range("O34").Value 'Did the agent follow appropriate closing procedure? - Earned Points
.Cells(iRow, 24).Value = shForm.Range("L34").Value 'Did the agent follow appropriate closing procedure? - Comment
'Courtesy & Communication - 2
.Cells(iRow, 25).Value = shForm.Range("J38").Value 'Did agent sound polite and courteous on the call ? - Audit Result
.Cells(iRow, 26).Value = shForm.Range("N38").Value 'Did agent sound polite and courteous on the call ? - Applicable Points
.Cells(iRow, 27).Value = shForm.Range("O38").Value 'Did agent sound polite and courteous on the call ? - Earned Points
.Cells(iRow, 28).Value = shForm.Range("L38").Value 'Did agent sound polite and courteous on the call ? - Comment
.Cells(iRow, 29).Value = shForm.Range("J39").Value 'Did agent empathize with customer's issue ? - Audit Result
.Cells(iRow, 30).Value = shForm.Range("N39").Value 'Did agent empathize with customer's issue ? - Applicable Points
.Cells(iRow, 31).Value = shForm.Range("O39").Value 'Did agent empathize with customer's issue ? - Earned Points
.Cells(iRow, 32).Value = shForm.Range("L39").Value 'Did agent empathize with customer's issue ? - Comment
.Cells(iRow, 33).Value = shForm.Range("J40").Value 'Did agent modulate his pitch and volume according to customer's ? - Audit Result
.Cells(iRow, 34).Value = shForm.Range("N40").Value 'Did agent modulate his pitch and volume according to customer's ? - Applicable Points
.Cells(iRow, 35).Value = shForm.Range("O40").Value 'Did agent modulate his pitch and volume according to customer's ? - Earned Points
.Cells(iRow, 36).Value = shForm.Range("L40").Value 'Did agent modulate his pitch and volume according to customer's ? - Comment
.Cells(iRow, 37).Value = shForm.Range("J41").Value 'Did the agent follow the call transfer protocol? - Audit Result
.Cells(iRow, 38).Value = shForm.Range("N41").Value 'Did the agent follow the call transfer protocol? - Applicable Points
.Cells(iRow, 39).Value = shForm.Range("O41").Value 'Did the agent follow the call transfer protocol? - Earned Points
.Cells(iRow, 40).Value = shForm.Range("L41").Value 'Did the agent follow the call transfer protocol? - Comment
.Cells(iRow, 41).Value = shForm.Range("J42").Value 'Did the agent follow the correct Hold procedure ? - Audit Result
.Cells(iRow, 42).Value = shForm.Range("N42").Value 'Did the agent follow the correct Hold procedure ? - Applicable Points
.Cells(iRow, 43).Value = shForm.Range("O42").Value 'Did the agent follow the correct Hold procedure ? - Earned Points
.Cells(iRow, 44).Value = shForm.Range("L42").Value 'Did the agent follow the correct Hold procedure ?- Comment
'Issue Resolution - 3
.Cells(iRow, 45).Value = shForm.Range("J46").Value 'Did the agent authenticate customer and confirm the issue? - Audit Result
.Cells(iRow, 46).Value = shForm.Range("N46").Value 'Did the agent authenticate customer and confirm the issue? - Applicable Points
.Cells(iRow, 47).Value = shForm.Range("O46").Value 'Did the agent authenticate customer and confirm the issue? - Earned Points
.Cells(iRow, 48).Value = shForm.Range("L46").Value 'Did the agent authenticate customer and confirm the issue? - Comment
.Cells(iRow, 49).Value = shForm.Range("J47").Value 'Did the agent do effective probing on the call? - Audit Result
.Cells(iRow, 50).Value = shForm.Range("N47").Value 'Did the agent do effective probing on the call? - Applicable Points
.Cells(iRow, 51).Value = shForm.Range("O47").Value 'Did the agent do effective probing on the call? - Earned Points
.Cells(iRow, 52).Value = shForm.Range("L47").Value 'Did the agent do effective probing on the call? - Comment
.Cells(iRow, 53).Value = shForm.Range("J48").Value 'Did the agent provide accurate and complete resolution on the call? - Audit Result
.Cells(iRow, 54).Value = shForm.Range("N48").Value 'Did the agent provide accurate and complete resolution on the call? - Applicable Points
.Cells(iRow, 55).Value = shForm.Range("O48").Value 'Did the agent provide accurate and complete resolution on the call? - Earned Points
.Cells(iRow, 56).Value = shForm.Range("L48").Value 'Did the agent provide accurate and complete resolution on the call? - Comment
.Cells(iRow, 57).Value = shForm.Range("J49").Value 'Did the agent provide clear understanding of the issue to the customer? - Audit Result
.Cells(iRow, 58).Value = shForm.Range("N49").Value 'Did the agent provide clear understanding of the issue to the customer? - Applicable Points
.Cells(iRow, 59).Value = shForm.Range("O49").Value 'Did the agent provide clear understanding of the issue to the customer? - Earned Points
.Cells(iRow, 60).Value = shForm.Range("L49").Value 'Did the agent provide clear understanding of the issue to the customer? - Comment
.Cells(iRow, 61).Value = shForm.Range("J50").Value 'Did the agent process the request as promised to the customer? - Audit Result
.Cells(iRow, 62).Value = shForm.Range("N50").Value 'Did the agent process the request as promised to the customer? - Applicable Points
.Cells(iRow, 63).Value = shForm.Range("O50").Value 'Did the agent process the request as promised to the customer? - Earned Points
.Cells(iRow, 64).Value = shForm.Range("L50").Value 'Did the agent process the request as promised to the customer? - Comment
'Case Documentation
.Cells(iRow, 65).Value = shForm.Range("J54").Value 'Agent documented the case correctly - Audit Result
.Cells(iRow, 66).Value = shForm.Range("N54").Value 'Agent documented the case correctly - Applicable Points
.Cells(iRow, 67).Value = shForm.Range("O54").Value 'Agent documented the case correctly - Earned Points
.Cells(iRow, 68).Value = shForm.Range("L54").Value 'Agent documented the case correctly - Comment
.Cells(iRow, 69).Value = shForm.Range("J55").Value 'Agent escalated the case to the right department with all relevant details - Audit Result
.Cells(iRow, 70).Value = shForm.Range("N55").Value 'Agent escalated the case to the right department with all relevant details - Applicable Points
.Cells(iRow, 71).Value = shForm.Range("O55").Value 'Agent escalated the case to the right department with all relevant details - Earned Points
.Cells(iRow, 72).Value = shForm.Range("L55").Value 'Agent escalated the case to the right department with all relevant details - Comment
'Process Compliance
.Cells(iRow, 73).Value = shForm.Range("J59").Value 'Did agent offer discount against SOP without prior approval from TL? - Audit Result
.Cells(iRow, 74).Value = shForm.Range("N59").Value 'Did agent offer discount against SOP without prior approval from TL? - Applicable Points
.Cells(iRow, 75).Value = shForm.Range("O59").Value 'Did agent offer discount against SOP without prior approval from TL? - Earned Points
.Cells(iRow, 76).Value = shForm.Range("L59").Value 'Did agent offer discount against SOP without prior approval from TL? - Comment
.Cells(iRow, 77).Value = shForm.Range("J60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Audit Result
.Cells(iRow, 78).Value = shForm.Range("N60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Applicable Points
.Cells(iRow, 79).Value = shForm.Range("O60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Earned Points
.Cells(iRow, 80).Value = shForm.Range("L60").Value 'Did the agent modified case fields to avoid CSAT going to the customer? - Comment
'Others
.Cells(iRow, 81).Value = shForm.Range("L11").Value 'Feedback Shared Yes/No
.Cells(iRow, 82).Value = shForm.Range("B64").Value 'Feedback/Remarks
.Cells(iRow, 83).Value = Application.UserName 'Updated By
.Cells(iRow, 84).Value = [Now()] 'Updated Date
.Range("A2").Select
End With
End SubVBA Function to get confirmation before submitting the data and call all the required functions before resetting the form
' Procedure to call all the functions to transfer the data
Sub SaveData()
Dim sAuditorName As String
Dim shForm As Object
Dim iMsg As Integer
iMsg = MsgBox("Do you want to submit this form?", vbYesNo + vbQuestion, "Submit Confirmation")
If iMsg = vbNo Then Exit Sub
Set shForm = ThisWorkbook.Sheets("Form")
If CheckEntry = False Then
Exit Sub
Else
sAuditorName = shForm.Range("L9").Value
Application.StatusBar = "Saving Data..."
Call Transfer
Application.StatusBar = "Drafting Email..."
Call Feedback_Email
Application.StatusBar = "Reseting Form..."
Call InitializeSheet
Application.StatusBar = "Done!"
Application.StatusBar = False
If Trim(sAuditorName) <> "" Then
shForm.Range("L9").Value = Trim(sAuditorName)
End If
MsgBox "Call audit score and summary updated successfully!"
ThisWorkbook.Save
Exit Sub
End If
End SubThis is all about the Call Center Transaction Monitoring Form in Excel and VBA. As this is free of cost, you can download the template after clicking on below button. Worksheet, Workbook and VBA password is thedatalabs .
Please watch our YouTube tutorials. Thanks!
Click on below button to download the template.
Excellent Video…. Thank you so much I am searching since long time for video like this. thanks lot…
please can you guide me how to design tables for a employee hiring and transfer maintenance DB in various branches of a company for employees. please.
1. DB should allow to assign employees to vacant posts, if already assigned it should show it is already assigned.
2.Once employee is assigned to a Post then status of post should change to filled from the date of appointment to till the date of removal/resignation/transfer of employee.
3.If employee resigned and transferred, then the status of post should become Vacant and then it should allow us to assign other employees to that post from the date of vacant
4.DB should record various employees worked in a post for different periods.
5.DB should show us no of posts filled and no of post vacant at any time.
please guide or make video on this please… I am desperately needed this please.