TextBox and Listbox in Excel Vba

November 23, 2024

I have a TextBox and a Listbox in EXCEL VBA project.
I need to insert value using textbox control.
Every time i click on enter, the value in the textbox control is added to the listbox below, the textbox control is emptied and I need to set setfocus on the textbox control for a new insertion.
I have a problem with the setfocus, it does not work!
I can’t set the setfocus on the textbox control after each insertion.
I have also used events: AFTER UPDATE, BEFORE UPDATE, CHANGE, ENTER, KEY DOWN and KEY UP, with the same result.

I use this code

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

If KeyCode <> 13 Then Exit Sub
Me.ListBox1.AddItem Me.TextBox1.Text
Me.TextBox1.Text = ""
Me.TextBox1.SetFocus
End Sub

>Solution :

Your code looks mostly correct but I have often found issues with SetFocus in VBA are due to timing or conflicts with how the event is being handled. You need to use a slight delay or verifying that no other control or operation interferes.

Application.OnTime introduces a slight delay before setting the focus back to the TextBox. This will ensure that SetFocus is being executed after all the key-handling events are finished.

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

    If KeyCode = 13 Then ' Check if Enter key is pressed
        Me.ListBox1.AddItem Me.TextBox1.Text ' Add item to ListBox
        Me.TextBox1.Text = "" ' Clear TextBox

        Application.OnTime Now, "SetFocusToTextBox"
    End If

End Sub

Private Sub SetFocusToTextBox()
    Me.TextBox1.SetFocus
End Sub