How to add autofill to all the textboxes

Question

I am new to vba and I want to learn. Please help me with the following:

I have this application(see the picture) which I use to enter data in a worksheet (it is more easy to use compared to completing manually). It is basically an inventory management system. When I add a product in there I can choose between sale or purchase.

The next thing I would like to implement is to autofill the form whenever I want to add a sale (considering it was added as a purchase in the first place), based on a serial code for example. This would be very useful because I wont have to complete all the text boxes again when I enter a sale in the database

Do you have any ideas about how I could do this?

Kind regard, Traian.

Answer 1

So, basically I shouldn't help since you have not done your research, but I did find it interesting to see if I could create such a function.

You wont be able to simply paste the code but it does work exactly as a autofill.

This is the "data" source I used for the autofill, it's looking for the left value and will autocomplete that textbox, as well as a secondary textbox with the value from column C. This would work with n numbers of autofills.

I only used 2 different fields to test this idea, disregard the labels. This is how it looked without typing anything.

As soon as you start to type, the "autofill" appears.

If you were to "hover" over the autofill, it will turn a different color, as well as all the input sheets, the input sheet also now includes the autofilled answers. if you were to "unhover"(hover over anything except the autofill) it will revert back to the second picture.

If I were to write this code again for a real project, I would change a couple of thing.

1. There might be leftover code from my testing, I would remove this.
2. I would use global variables so to avoid declaring variables more than one time.
3. I would name the textboxes and label in a better way.
4. I would complicate the textboxes with labels as to get the text to align in center.
5. The order of the code might not be the best for you to understand.
6. etc.

Here is the code:

Private Sub Autofill_Click()
    Dim BestOption As Integer
    Dim ValueRange As Range
    Set ValueRange = Sheets("sheet1").Range("B8:B13")
    Dim Start As Range
    Set Start = Sheets("sheet1").Range("B7")
    
    BestOption = WorksheetFunction.Match(Autofill, ValueRange, 0)
    TextBox2 = Start.Offset(BestOption, 1)
    TextBox1 = Start.Offset(BestOption, 0)
    Autofill.Visible = False
    
    TextBox3.Visible = False
    TextBox4.Visible = False

End Sub



Private Sub TextBox1_Change()
    Dim Start As Range
    Dim ValueRange As Range
    Dim MatchCounter As Integer
    Set Start = Sheets("sheet1").Range("B7")
    Set ValueRange = Sheets("sheet1").Range("B8:B13")
    
    If TextBox1 = "" Then
        Autofill.Visible = False
    Else
        'Call FindClosestMatch(TextBox1)
        Autofill.Visible = True
        Autofill = Start.Offset(FindClosestMatch(TextBox1) + 1, 0)
    End If
End Sub



Function FindClosestMatch(Entry As String) As Integer
    Dim BestOption As Integer
    Dim Start As Range
    Set Start = Sheets("sheet1").Range("B7")
    
    Dim MyArray(6) As String
    Dim i As Integer
    Dim j As Integer
    Dim iChar As String
    Dim EntryChar As String
    
    For i = 0 To 5
        MyArray(i) = Start.Offset(i + 1, 0)
    Next i
    
    
    
    For j = 1 To Len(Entry)
        EntryChar = Mid(Entry, j, 1)
        
        For i = 0 To 5
            If EntryChar = "" Then
                Exit For
            End If
            
            iChar = Mid(MyArray(i), j, 1)
            If iChar = EntryChar Then
                BestOption = i
                Else
                MyArray(i) = "................."
            End If
        Next i
    Next j
    
    FindClosestMatch = BestOption
End Function


'hover
Private Sub Autofill_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Autofill.BackColor = &H80000002
    TextBox3.BackColor = &H80000002
    TextBox4.BackColor = &H80000002
    
    Dim BestOption As Integer
    Dim ValueRange As Range
    Set ValueRange = Sheets("sheet1").Range("B8:B13")
    Dim Start As Range
    Set Start = Sheets("sheet1").Range("B7")
    
    BestOption = WorksheetFunction.Match(Autofill, ValueRange, 0)
    TextBox3.Visible = True
    TextBox4.Visible = True
    TextBox4 = Start.Offset(BestOption, 1)
    TextBox3 = Start.Offset(BestOption, 0)
End Sub


Private Sub TextBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Call test
End Sub
Private Sub TextBox2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Call test
End Sub
Private Sub TextBox3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Call test
End Sub
Private Sub TextBox4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Call test
End Sub

Private Sub UserForm_Click()
Call test
Autofill.Visible = False
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call test
End Sub


Sub test()
    Autofill.BackColor = &H80000000
    TextBox1.BackColor = &H80000005
    TextBox2.BackColor = &H80000005
    
    TextBox3.Visible = False
    TextBox4.Visible = False
End Sub

Problem to think about:

1. The autofill always give the best answer, even if no good answer exist. In those cases, the best answer is the first answer in the data structure.
2. It is case sensitive.
3. One charachter wrong and you wont find your answer.

Notes: I used 4 textboxes, number 1 and 2 are sitting on top of each other, and number 2 and 4 are on top of each other. This was done to not lose the already typed input if you accidently hovered over the autofill.