Userform Textbox Autocomplete

Posted on March 28, 2018 by Dick Kusleika

I’m working on a project where the user types some stuff into a textbox. A good portion of the time, what the user will type will match one of the last few things he typed. I wanted the textbox to autocomplete if there was a match to a list. Pretty simple, I think. For purposes of this demonstration, I’m going to match to a list of random sentences in a listbox.

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

    Dim vaRecent As Variant
    Dim i As Long
    Dim sEntered As String
    
    If Not mbEventsDisabled Then
        sEntered = Me.TextBox1.Text
        If Len(sEntered) <= 5 Then
            vaRecent = Me.ListBox1.List
            For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)
                If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then
                    mbEventsDisabled = True
                        With Me.TextBox1
                            .Text = vaRecent(i, 0)
                            .SelStart = Len(sEntered)
                            .SelLength = Len(vaRecent(i, 0))
                        End With
                    mbEventsDisabled = False
                    Exit For
                End If
            Next i
        End If
    End If
    
End Sub

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

Dim vaRecent As Variant

Dim i As Long

Dim sEntered As String

If Not mbEventsDisabled Then

sEntered = Me.TextBox1.Text

If Len(sEntered) <= 5 Then

vaRecent = Me.ListBox1.List

For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)

If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then

mbEventsDisabled = True

With Me.TextBox1

.Text = vaRecent(i, 0)

.SelStart = Len(sEntered)

.SelLength = Len(vaRecent(i, 0))

End With

mbEventsDisabled = False

Exit For

End If

Next i

End If

End Sub

I had to use that old disable events in a userform trick otherwise setting the .Text property would call the change event again.

I only look at the first five characters. After that, you just have to type what you want. If there’s a match, I set the .Text property to the matching sentence and set the selection so that the user can continue typing. It all worked very nicely except for backspacing. In the above screenshot, I’ve typed He but the textbox contains the whole sentence. If I hit backspace in this situation, I delete the highlighted portion and I’m left with He. Backspace does nothing.

I was hoping to find a simple and elegant solution. Instead, I did this.

Private bBackSpace As Boolean
Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

    Dim vaRecent As Variant
    Dim i As Long
    Dim sEntered As String
    
    If Not mbEventsDisabled Then

        sEntered = Me.TextBox1.Text
        If bBackSpace And Len(sEntered) > 0 Then sEntered = Left$(sEntered, Len(sEntered) - 1)
        
        If Len(sEntered) < 5 Then
            vaRecent = Me.ListBox1.List
            
            For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)
                If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then
                    mbEventsDisabled = True
                        With Me.TextBox1
                            .Text = vaRecent(i, 0)
                            .SelStart = Len(sEntered)
                            .SelLength = Len(vaRecent(i, 0))
                        End With
                    mbEventsDisabled = False
                    Exit For
                End If
            Next i
        End If
    End If
    
End Sub

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

Private bBackSpace As Boolean

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

Dim vaRecent As Variant

Dim i As Long

Dim sEntered As String

If Not mbEventsDisabled Then

sEntered = Me.TextBox1.Text

If bBackSpace And Len(sEntered) > 0 Then sEntered = Left$(sEntered, Len(sEntered) - 1)

If Len(sEntered) < 5 Then

vaRecent = Me.ListBox1.List

For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)

If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then

mbEventsDisabled = True

With Me.TextBox1

.Text = vaRecent(i, 0)

.SelStart = Len(sEntered)

.SelLength = Len(vaRecent(i, 0))

End With

mbEventsDisabled = False

Exit For

End If

Next i

End If

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

bBackSpace = KeyCode = 8

End Sub

I’m using a module-level variable to determine if the backspace was pressed while in the textbox. If it was and there’s still at least one character, I simply shorten the sEntered variable by one character. That leaves the whole SelStart and SelLength mechanism working as expected.

In an userform list all available fonts

Posted on March 20, 2012October 3, 2012 by Tushar Mehta

The motivation for this tip was to share how to

1) dynamically add controls to a userform
2) respond to events for these controls, and
3) specifically respond to events using a callback procedure that is located in another class module!

Since this may come across as a fairly technical topic, this tip utilizes the above capabilities to provide a functional solution:

1) list in an userform the names of all available fonts with each name shown using that font,
2) hover over the option button associated with a font to see a sample of every English keyboard character in that font,
3) click on the option button to select the font, and, finally,
4) use this capability to programmatically get the user’s selection, if any.

Below is an example of the font selector in action. Each OptionButton shows the name of one available font using the font itself. At the same time, the control tool tip shows the font name in English (see the Wide Latin tip). A sample of how every keyboard character will look in that font appears below the font selector frame.

The motivation for this example was a Daily Dose of Excel blog post by Michael (http://www.dailydoseofexcel.com/archives/2012/03/14/getting-a-font-list-to-a-combo-box-2/). He used a combo box to list the fonts available to Excel leveraging a technique shown in a tip by John Walkenbach (http://www.j-walk.com/ss/excel/tips/tip79.htm).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1054%20show%20fonts%20in%20userform.shtml

Tushar Mehta

Passing Arguments to a Userform

Posted on July 22, 2004January 16, 2019 by Dick Kusleika

The Initialize event of userforms doesn’t take an argument, so you can’t pass data directly to it. Here are three ways to pass a variable to a userform.

Global Variable

I avoid using global variables whenever practical, but depending on the complexity of the application, it may be the best choice. Create a global variable in a standard module and set it before showing the form.

Public gsMyVariable As String

Sub ShowForm()

    gsMyVariable = "Pass to form"
    UserForm1.Show
    
End Sub

Public gsMyVariable As String

Sub ShowForm()

gsMyVariable = "Pass to form"

UserForm1.Show

End Sub

Then you can use that variable in the userform’s code

Private Sub CommandButton1_Click()

    MsgBox gsMyVariable

End Sub

Private Sub CommandButton1_Click()

MsgBox gsMyVariable

End Sub

The Tag Property

If you’re not using the Tag property for something else, you can store some data in it for later use. You just need to load the form before you set the property, then show it.

Sub ShowFormTag()

    Load UserForm2
    UserForm2.Tag = "Pass to form"
    UserForm2.Show
    
End Sub

Private Sub CommandButton1_Click()

    MsgBox Me.Tag
    
End Sub

Sub ShowFormTag()

Load UserForm2

UserForm2.Tag = "Pass to form"

UserForm2.Show

End Sub

Private Sub CommandButton1_Click()

MsgBox Me.Tag

End Sub

Properties

Because a userform is just a class module with a built-in user interface, you can create properties in the form’s module. The form’s module might look like this

Private msMsg As String

Private Sub CommandButton1_Click()
    MsgBox msMsg
End Sub

Property Let MyProp(sText As String)
    msMsg = sText
End Property

Private msMsg As String

Private Sub CommandButton1_Click()

MsgBox msMsg

End Sub

Property Let MyProp(sText As String)

msMsg = sText

End Property

and showing the form from a standard module

Sub ShowFormProp()

    Dim frmMyForm As UserForm3
    
    Set frmMyForm = New UserForm3
    frmMyForm.MyProp = "Pass to form"
    frmMyForm.Show
    
End Sub

Sub ShowFormProp()

Dim frmMyForm As UserForm3

Set frmMyForm = New UserForm3

frmMyForm.MyProp = "Pass to form"

frmMyForm.Show

End Sub

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Tag: userform

Userform Textbox Autocomplete

In an userform list all available fonts

Passing Arguments to a Userform