Limit a list box

[johnny45]

Trying to use some code I found on a blog on how to limit a ListBox.

I have multiple worksheets and I would like to run this user form with any of the sheets active and NOT the one containing the data for the form....

Private Sub UserForm_Initialize()

With Sheet1
   .Cells.Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
'[URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1438167&page=1[/URL]
      Select Case .[B1].CurrentRegion.Rows.Count
        Case 2
            Me.lbxCustomers.RowSource = .[B2].address
        Case Is > 2
            Me.lbxCustomers.RowSource = .Range(.[B2], .[B2].End(xlDown)).address
      End Select


End With

End Sub

Private Sub tbxFind_Change()
   Dim vList As Variant
   
   'Read the original list
   With Sheet1
   vList = .Range(.[B2], .Cells(Rows.Count, 2).End(xlUp)).value  
   End With
   'Convert it to a 1D array
   vList = Application.Transpose(vList)
   
   'Filter it
   vList = Filter(SourceArray:=vList, _
                  Match:=tbxFind.value, _
                  Include:=True, _
                  Compare:=vbTextCompare)
   
   'Send it to the listbox
   lbxCustomers.List = vList
   If lbxCustomers.ListCount = 0 Then
   
'
Customers.txtName.Text = Me.tbxFind.value
Unload Me
   Customers.Show
   End If

End Sub

1- the list box does NOT get populated with values from sheet 1 if it is not active
2- I get a type mismatch on the change event.....

 

[SkipVought]

Hi,

Me.lbxCustomers.RowSource = .name & "!" & .Range(.[B2], .[B2].End(xlDown)).address

Type mismatch on what statement?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[johnny45]

Thanks Skip that fixed part of it...I still get the type mismatch here:

   vList = Filter(SourceArray:=vList, _
                  Match:=tbxFind.value, _
                  Include:=True, _
                  Compare:=vbTextCompare)

 

[SkipVought]

You do a transpose on vList. Is this a MULTI-dimension array?

sourcearray Required. One-dimensional array of strings to be searched.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[johnny45]

vList = .Name & "!" & .Range(.[B2], .Cells(Rows.Count, 2).End(xlUp)).value

I guess this would be 1 dimensional ?

 

[SkipVought]

Exactly what values are in vList?

What is tbxFind.value?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[johnny45]

Exactly what values are in vList:
There is on ly on erecrod now and it is the word "Test"

What is tbxFind.value?
I just type the letter t and I get the error

 

[SkipVought]

vlist must be ONE-dimensional
vout must be zero based

    Dim vList(1), vOut, i As Integer
    
    vList(0) = [A1]
    vList(1) = [A2]
    
   vOut = Filter(SourceArray:=vList, _
                  Match:="t", _
                  Include:=True, _
                  Compare:=vbTextCompare)
    For i = 0 To UBound(vOut)
        MsgBox vOut(i)
    Next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[johnny45]

this is the original code:

Private Sub tbxFind_Change()
   Dim vList As Variant
   
   'Read the original list
   vList = Sheet1.Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
   
   'Convert it to a 1D array
   vList = Application.Transpose(vList)
   
   'Filter it
   vList = Filter(SourceArray:=vList, _
                  Match:=tbxFind.Value, _
                  Include:=True, _
                  Compare:=vbTextCompare)
   
   'Send it to the listbox
   lbxCustomers.List = vList
End Sub

Private Sub UserForm_Initialize()
   'Read the original list
   lbxCustomers.List = Sheet1.Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
End Sub

I just wanted to modify so that it ONLY populates from sheet1 regardless of te active sheet.
I ran this code and it works fine....

So I guess somehow in my interpretation to get it to populate ONLY from sheet 1.....