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

[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

Good monday to you and all