ListBox - Search Excel Range

[dj44]

folks,
good day again.

i was working on my listbox to make it search the range in excel.

Well it worked but now it wont search any more.


I spent hours trying to figure it out, im not sure if i deleted something or where i need to add an public declaration?

please can some one see what the problem is


thank you

[mdmackillop]

Totally mystified as to what is search term, what is to be searched, what is expected result.

[dj44]

Hello M,

I was trying to simply filter my list box.

So if i enter the letter a it shows all the results that begin with a

I just wanted to filter my listbox results.

When i populated it before using an array in the userform it filtered correctly.

Then i changed the population method to using an excel range and this is where it stopped working

I dont know why

[mdmackillop]

You can't delete an item if you use RowSource

Private Sub UserForm_Initialize()
Dim r As Range
Set r = Sheets("Data").Range("D2:F30")
ListBox1.List = r.Value
End Sub

[dj44]

Hello M,

that worked - so im very happy i scoured all my code for this and i couldnt find it anywhere

but woe me the reset button has failed

I did this




Option Explicit

Public r As Range
'----------------------------------------

Private Sub cmdResetList_Click()

'Me.ListBox1.List = arrList

Me.ListBox1.List = r.Value ' << Failed

Me.TextBox1 = vbNullString
End Sub


i change one tiny thing and it all collapses like the leaning tower

[mdmackillop]

Option Explicit

Private Sub UserForm_Initialize()
Dim r As Range
Set r = Sheets("Data").Range("D2:F30")
ListBox1.List = r.Value
End Sub

Private Sub TextBox1_change()
Dim strFilter As String
Dim lngIndex As Long
Me.ListBox1.Clear
UserForm_Initialize
strFilter = LCase(Me.TextBox1.Text) & "*"
For lngIndex = ListBox1.ListCount - 1 To 0 Step -1
If Not LCase(ListBox1.List(lngIndex, 0)) Like strFilter Then
ListBox1.RemoveItem (lngIndex)
End If
Next
End Sub


Private Sub cmdResetList_Click()
Me.ListBox1.Clear
UserForm_Initialize
Me.TextBox1 = vbNullString
End Sub




Edit: Textbox1 code changed to reset listbox

[mdmackillop]

it all collapses like the leaning tower
The leaning tower has been there for 645 years :devil2:

[dj44]

Thank you M,

for mondays magnfificient magnanimus rescue of this listbox.

I was trying to move this to excel as an addin, but what i imagined a easy feat fell flat since yesterday.

I repainted the userform? or something like that according to stack

but anyways its a long story.

My reset button is working and all the other bits n bob in place finally - so im happy

But I tell you userforms Listboxes and arrays - i can never to this day get the hang of

they just dont like me :grinhalo:

Good monday to you and all