VBA UserForm || Textbox Autofill Using Worksheets

[sachinns]

Hi Friends,

I Have a userform consisting of some textbox. I wanted to add one feature to the textbox1 like Auto filling when we write.

Example : I have a sheet where 2 columns are there. One is A and another is B. I have some datas in these columns as well.

So when user starts write in textbox1 , then it should autofill the textbox using the datas available in the Column A.

Can somebody help me on this.


Thanks

 

[mikerickson]

You could put code like this in the code module for the userform

Dim DisableUFEvents As Boolean

Private Sub TextBox1_Change()
    Dim cText As String, sheetRow As Variant
    If DisableUFEvents Then Exit Sub
    With TextBox1
        cText = .Text
        sheetRow = Application.Match(.Text & "*", WordsRange, 0)
        If IsNumeric(sheetRow) Then
            DisableUFEvents = True
            .Text = WordsRange.Cells(sheetRow, 1).Text
            .SelStart = Len(cText)
            .SelLength = 100
            DisableUFEvents = False
        End If
    End With
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    DisableUFEvents = (KeyCode = vbKeyBack)
End Sub

Function WordsRange() As Range
    Set WordsRange = Sheet1.Range("A:A")
End Function

 

[sachinns]

I am getting error.

Run-time error '1004':

Method 'Activate' of object'_Worksheet' failed

My worksheet name is "Formula" and Range "A".

 

[mikerickson]

Change the WordsRange function to match your situation.

 

[sachinns]

Thanks Its working.

But it is not working correctly. The backspace is not working . Because of that i am not able to change the auto fill.

Example : i have Animal-Dog , Animal-Cat , Animal-Lion is the sheet as values. When i write automatically the textbox will be filled with Animal-Dog. i am note able remove the last word using backspace to replace Dog with Cat.


Please help me.

 

[sachinns]

@mikerickson

 

[mikerickson]

That code will auto-fill with the first word on the list that matches the users partial entry.
If your sheet has the entries in this order
Animal-Dog
Animal-Cat
Animal-Lion

and the user types "ani", Animal-Dog will appear, with mal-Dog highlighted.
the user can tab out
or continue typing mal-c and Animal-Cat will be the suggested entry.

If you want a dropdown to appear with a list of matching words to be selected from, you can use a ComboBox instead of a TextBox with code like this

Dim DisableUFEvents As Boolean

Private Sub ComboBox1_Change()
    Dim cText As String, sheetRow As Variant
    Dim WordOptions As Variant
    If DisableUFEvents Then Exit Sub
    With ComboBox1
        cText = .Text
        WordOptions = MatchingWords(cText)
        
        If 0 < UBound(WordOptions) Then
            ComboBox1.List = WordOptions
            
            If .ListCount = 1 Then
                DisableUFEvents = True
                .Text = .List(0)
                .SelStart = Len(cText)
                .SelLength = 100
                DisableUFEvents = False
            Else
                .DropDown
            End If
        End If
    End With
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    DisableUFEvents = (KeyCode = vbKeyBack)
End Sub

Function WordsRange() As Range
    With Sheet1.Range("A:A")
        Set WordsRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
End Function

Function MatchingWords(ByVal StartOfWord As String) As Variant
    Dim oneCell As Range, Pointer As Long
    Dim Result() As String
    StartOfWord = LCase(StartOfWord)
    ReDim Result(1 To WordsRange.Cells.Count)
    For Each oneCell In WordsRange
        If LCase(oneCell.Text) Like StartOfWord & "*" Then
            Pointer = Pointer + 1
            Result(Pointer) = oneCell.Text
        End If
    Next oneCell
    If 0 = Pointer Then
        ReDim Result(0 To 0)
    Else
        ReDim Preserve Result(1 To Pointer)
    End If
    MatchingWords = Result
End Function

Private Sub UserForm_Initialize()
    ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
End Sub

 

[sachinns]

@mikerickson

The client wants to be a textbox.

 

[sachinns]

@ mikerickson

Thanks for the code. Thank you very much. I wanted to add one more thing to this code. Please help me on that.

So Based on the selection of this combobox1 , i want to generate value in textbox2.

Example : User selected "Animal-Dog" in combobox1 .

There is sheet where this value is present , Range "A" . Corresponding to this value there is another value present in Range "B".
That Value should come in the textbox2.

Can u please help me on this.


I was having an existing code previously. Can u add this code to your code.

Dim i As Long, Lastrow As Long
Set ws = ThisWorkbook.Worksheets("Formula")
ws.Activate
Lastrow = Sheets("Formula").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Formula").Cells(i, "A").Value = (Me.TextBox1) Or _
Sheets("Formula").Cells(i, "A").Value = Val(Me.TextBox1) Then
Me.TextBox2 = Sheets("Formula").Cells(i, "B").Value
Else
TextBox2.Value = ""
End If
Next


Note : textbox changed to combobox

 

[sachinns]

I added my code to your code.

Private Sub ComboBox3_Change()
Set ws = ThisWorkbook.Worksheets("Formula")
ws.Activate
Dim DisableUFEvents As Boolean
    Dim cText As String, sheetRow As Variant
    Dim WordOptions As Variant
    If DisableUFEvents Then Exit Sub
    With ComboBox3
        cText = .Text
        WordOptions = MatchingWords(cText)
        
        If 0 < UBound(WordOptions) Then
            ComboBox3.List = WordOptions
            
            If .ListCount = 1 Then
                DisableUFEvents = True
                .Text = .List(0)
                .SelStart = Len(cText)
                .SelLength = 100
                DisableUFEvents = False
            Else
                .DropDown
            End If
        End If
    End With
    
Dim i As Long, Lastrow As Long


Lastrow = Sheets("Formula").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Formula").Cells(i, "A").Value = (Me.ComboBox3) Or _
Sheets("Formula").Cells(i, "A").Value = Val(Me.ComboBox3) Then
Me.TextBox2 = Sheets("Formula").Cells(i, "B").Value
End If
Next
    
End Sub


Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim DisableUFEvents As Boolean
    DisableUFEvents = (KeyCode = vbKeyBack)
End Sub


Function WordsRange() As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Formula")
ws.Activate
    With Range("A:A")
        Set WordsRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
End Function


Function MatchingWords(ByVal StartOfWord As String) As Variant
Set ws = ThisWorkbook.Worksheets("Technical Attribute")
ws.Activate
Dim DisableUFEvents As Boolean
    Dim oneCell As Range, Pointer As Long
    Dim Result() As String
    StartOfWord = LCase(StartOfWord)
    ReDim Result(1 To WordsRange.Cells.Count)
    For Each oneCell In WordsRange
        If LCase(oneCell.Text) Like StartOfWord & "*" Then
            Pointer = Pointer + 1
            Result(Pointer) = oneCell.Text
        End If
    Next oneCell
    If 0 = Pointer Then
        ReDim Result(0 To 0)
    Else
        ReDim Preserve Result(1 To Pointer)
    End If
    MatchingWords = Result
End Function

Private Sub UserForm_Initialize()
    ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
End Sub

It is working Perfectly Now. One small Bug.

Based on my selection in combobox3 the values are coming in textbox2. But bug is that suppose if i remove the value from combobox3 the corresonding value in the textbox2 is not getting removed automatically. Its only getting updated when i change the value in combobox3.

Please help me on this.