Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 27th 21, 07:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2018
Posts: 25
Default Listbox with dynamic strings

I have a Userform that contains one TextBox and one ListBox. The ListBox will initially contain about 300 unique string values.

If a user types a short substring into the TextBox, I would like to only visually display the lines that contain that substring. Therefore, the items in the ListBox will dynamically change, depending on the substring entered in the TextBox.

What would be an efficient way to implement this? Would I have to store the strings in an array and constantly update both the array and ListBox?

Thanks!

  #2   Report Post  
Old January 28th 21, 10:13 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 86
Default Listbox with dynamic strings

"Tatsujin" wrote in message
I have a Userform that contains one TextBox and one ListBox. The ListBox
will initially contain about 300 unique string values.

If a user types a short substring into the TextBox, I would like to only
visually display the lines that contain that substring. Therefore, the
items in the ListBox will dynamically change, depending on the substring
entered in the TextBox.

What would be an efficient way to implement this? Would I have to store
the strings in an array and constantly update both the array and ListBox?


One of several ways, in a light test this worked for me

With sample text in A1:A300, a form with a listbox and textbox, type in the
textbox

Private Sub TextBox1_Change()
Dim i As Long
Dim sMatch As String
Dim arr
arr = Range("A1:A300").Value
sMatch = LCase$("*" & Me.TextBox1 & "*")
With Me.ListBox1
.Clear
For i = 1 To UBound(arr)
If LCase(arr(i, 1)) Like sMatch Then
.AddItem arr(i, 1)
End If
Next
End With
End Sub

Peter T




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use a listbox as a dynamic array? Robert Crandal[_2_] Excel Programming 9 March 7th 13 08:38 PM
Dynamic link with 2 dependent listbox? Cam Excel Discussion (Misc queries) 0 April 8th 09 06:51 PM
DYNAMIC LISTBOX daphoenix Excel Programming 2 April 2nd 09 10:46 AM
dynamic fill of listbox - need help gonger Excel Programming 6 May 25th 06 07:16 PM
Dynamic Multi Column ListBox Ronbo Excel Programming 3 January 11th 05 07:59 PM


All times are GMT +1. The time now is 10:55 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017