Listbox needs referencing to correct Worksheet

Hi Guys,

I have a problem with my Listbox that it only references the correct worksheet (Sheet2) when that worksheet is active. Can someone help me code the following that it references the correct worksheet.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer

ListBox2.RowSource = ""

j = 0

For i = 2 To Range("B65536").End(xlUp).Row
If ComboBox2.Text = Cells(i, 3) Then
With ListBox2
.AddItem
.List(j, 0) = Cells(i, 2)
.List(j, 1) = Cells(i, 3)
.List(j, 2) = Cells(i, 4)
.List(j, 3) = Cells(i, 5)
.List(j, 4) = Cells(i, 6)
.List(j, 5) = Cells(i, 7)
.List(j, 6) = Cells(i, 8)
.List(j, 7) = Cells(i, 9)
End With
j = j + 1
End If
Next i

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox2
.ColumnCount = 8
.ColumnWidths = "100;128;60;60;60;60;60;60"
.RowSource = Sheet9.Range("B2:I600").Address
End With
End Sub

Please wrap your code with code tags as per forum's rule.
If you have not referenced a sheet, by default the code works on the active(Where ever the cursor is)

Please Login or Register  to view this content.