Excel Technical Service Entry Form

Data Transfer Between Sheets

With this form template, it’s easy and fast to register devices that come to the technical service for repair.
The template consists of three sheets :
📄MAIN
📄RECORDS
📄CUSTOMERS

On the MAIN sheet, there are combobox ,checkbox,spinbutton,buttons and text boxes. The entered data into the text boxes is saved to the corresponding columns on the RECORDS sheet when the button is clicked.

➪ The entered phone number into the textbox (TextBox9) is checked with VBA codes . If the value is not numeric, it is not allowed to enter it into the textbox.
Also, the character “-” (Chr (45)) is added between the numbers. Up to 12 characters can be entered in this textbox. ↓

For i = 1 To Len(TextBox9.Text)
If Not IsNumeric(Mid(TextBox9.Text, i, 1)) And Mid(TextBox9.Text, i, 1) <> Chr(45) Then
TextBox9.Text = Left(TextBox9.Text, Len(TextBox9.Text) - 1)
End If
Next i
If Len(TextBox9.Text) = 3 Then
TextBox9.Text = TextBox9.Text & Chr(45)
End If
If Len(TextBox9.Text) = 7 Then
TextBox9.Text = TextBox9.Text & Chr(45)
End If
TextBox9.MaxLength = 12 

➪ When checkbox on the sheet is selected, today’s date is added to text boxes opposite the Time Received and Device Delivery Date fields. The date opposite the Device Delivery Date field can be adjusted by clicking the spinbutton.

➪ The combobox is populated with data from column A on the Customers sheet. Value of the textbox (customer phone) under the combobox is taken from column B of the Customers sheet according to the value of the combobox.
A new customer record can be created by entering the value in the combobox and this textbox and after clicking the “Add To Customers” button. It is checked with a loop, and if the customer record already exists, new record is not created.

son = Sheets("Customers").Cells(Rows.Count, "A").End(xlUp).row
   On Error Resume Next
            
    bul = Empty
    Set ara = Sheets("Customers").Range("A2:A" & son).Find(ComboBox1, , xlValues, xlWhole)
     If Not ara Is Nothing Then
        adres = ara.Address
      Do
        bul = bul & ara.row & " -  " & Cells(ara.row, "A") & Chr(10)
        Set ara = Sheets("Customers").Range("A2:A" & son).FindNext(ara)
        Loop While Not ara Is Nothing And ara.Address <> adres
  MsgBox "This record already exists!" & vbCrLf, vbCritical, "Eksi30.com"
        ComboBox1.Activate
        Exit Sub
        End If 

The new value is instantly listed in the combobox automatically. Also, combobox items are sorted alphabetically.
Customers and phone numbers are easily displayed by combobox item selection. Customer record can be searched in the combobox.

➪ On the RECORDS sheet, between records can be searched by customer name. The filtering process can be done according to two dates selected from comboboxes on the sheet. The sum of the prices is shown in a textbox.
To filter the records between two dates, first the two combo boxes on the sheet are filled unique and sorted (from first date to last date).

We used the following VBA procedures to fill dates into the combo boxes as unique and sorted values : ↓

  Sub degis()
Dim i As Long
Dim MyList As Range
Dim cel As Range
Dim d As Variant, It As Variant, a As Variant

ComboBox1.Clear
Set d = CreateObject("Scripting.Dictionary")
Set MyList = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))

'Create list of unique items using a Dictionary object
On Error Resume Next
For Each It In MyList
d.Add It.Value, It.Value 'Add keys and items
Next

'Create an array of unique items
a = d.Items
'Sort the array
Rapidly_Sort a, 0, UBound(a)
ComboBox1.List() = a

For s = 0 To ComboBox1.ListCount
ComboBox1.List(s) = Format(ComboBox1.List(s), "dd.mm.yyyy")
Next
ComboBox2.List = ComboBox1.List
End Sub
Sub Rapidly_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim dusuk, High As Long
Dim Temp As Variant, List_Separator As Variant
dusuk = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
Do While (SortArray(dusuk) < List_Separator)
dusuk = dusuk + 1
Loop
Do While (SortArray(High) > List_Separator)
High = High - 1
Loop
If (dusuk <= High) Then
Temp = SortArray(dusuk)
SortArray(dusuk) = SortArray(High)
SortArray(High) = Temp
dusuk = dusuk + 1
High = High - 1
End If
Loop While (dusuk <= High)
If (First < High) Then Rapidly_Sort SortArray, First, High
If (dusuk < Last) Then Rapidly_Sort SortArray, dusuk, Last
End Sub