VBA userform listbox - hard set range to specific sheet

[torquil]

Hello Everyone, I am disapearing down a frustrating black hole here.
I have a working bit of VBA that populates a List box from a range, but i cannot get the range to be hard set to the specific sheet.
If i load the userform on any other sheet, it tries to using data on that sheet to populate the listbox and not the orginal source I want:


Private Sub tbSymbol_Change()
' Searchable List box for Symbol
Dim i As Integer
lbSymbol.Clear
lbSymbol.Visible = True
For i = 2 To Worksheets("Nasdaq live").Range("A1000").End(xlUp).Row
If UCase(Left(Cells(i, 1), Len(tbSymbol.Text))) = UCase(tbSymbol.Text) Then
lbSymbol.AddItem Cells(i, 1)
End If
Next i

End Sub




Can anyone see where i have gone wrong?

the code acts the same with or without Worksheets("Nasdaq live").

[Kenneth Hobs]

Private Sub tbSymbol_Change()
' Searchable List box for Symbol
Dim i As Integer

lbSymbol.Clear
lbSymbol.Visible = True

With Worksheets("Nasdaq live")
For i = 2 To .Range("A1000").End(xlUp).Row
If UCase(Left(.Cells(i, 1), Len(tbSymbol.Text))) = UCase(tbSymbol.Text) Then
lbSymbol.AddItem .Cells(i, 1)
End If
Next i
End With
End Sub

[Paul_Hossler]

Just "Cells" refers to the Activesheet

<dot>Cells refers to the With Worksheets( ....)

If you explicitly specified the WS, it should work, but Ken's is more conciser :doh:



Private Sub tbSymbol_Change()
' Searchable List box for Symbol
Dim i As Integer
lbSymbol.Clear
lbSymbol.Visible = True


For i = 2 To Worksheets("Nasdaq live").Range("A1000").End(xlUp).Row
If UCase(Left(Worksheets("Nasdaq live").Cells(i, 1), Len(tbSymbol.Text))) = UCase(tbSymbol.Text) Then
lbSymbol.AddItem Worksheets("Nasdaq live").Cells(i, 1)
End If
Next i

End Sub