VBA UserForm || Textbox Autofill Using Worksheets

sachinns

Board Regular
Joined
Jun 21, 2019
Messages
52
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You could put code like this in the code module for the userform
Code:
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
 
Upvote 0
I am getting error.

Run-time error '1004':

Method 'Activate' of object'_Worksheet' failed

My worksheet name is "Formula" and Range "A".
 
Upvote 0
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.
 
Upvote 0
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
Code:
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
 
Upvote 0
@ 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
 
Last edited:
Upvote 0
I added my code to your code.

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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top