Realtime filter of Listbox values through a Textbox

I have a Userform with one ListBox and one TextBox. The ListBox contains data and I want to filter these data depending on the text in the Textbox. Moreover, I want to actualize values in the ListBox during typing a text in the TextBox. This is very simple and I'll show you how to do it.

1) The Transpose method can transform a range values to single-dimensional array.
2) The ListBox has the List property which allows to insert an array values into the ListBox.
3) There is the Filter function in the VBA which returns a subset of an array values based on specified filter criteria.

If you know it, the solution is really a piece of cake.

1) Paste some data to range A1:A100.
2) Create a new Userform named UserForm1 with Textbox named TextBox1 and Listbox named ListBox1.
3) Paste the code below into the UserForm1 code module.
4) Run and try.

Select all

Dim sValues() As Variant

Private Sub UserForm_Initialize()
  sValues = Application.Transpose(Activesheet.Range("A1:A100").Value)
  
  ListBox1.List = sValues
End Sub

Private Sub TextBox1_Change()
  ListBox1.List = Filter(sValues, TextBox1.Text, True, vbTextCompare)
End Sub

Tweet