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 :
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.
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
As seen in the above picture , 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