Dynamic Customer Call Log Form in VBA

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.