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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,643
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
 

sachinns

Board Regular
Joined
Jun 21, 2019
Messages
52
I am getting error.

Run-time error '1004':

Method 'Activate' of object'_Worksheet' failed

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

sachinns

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,643
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
 

sachinns

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

sachinns

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

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,819
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top