RowSource vba Excel

[BAEdwards]

Please can someone tell me why when the listbox, PSFQueryResults, is populated it always draws it's data from the active worksheet and not Sheet2 (Sheet2 is the 'Code Name' for the sheet. The 'Tab Name' is Query)? I suspect I am missing something obvious.

Many thanks in advance,

Ben.

Dim queryrows As Integer
queryrows = Sheet2.UsedRange.Rows.Count

Dim rng As Range
Set rng = Sheet2.Range("D2:F" & queryrows)
Me.PSFQueryResults.RowSource = rng.Address

[AndreRet]

Not sure if this will help, try and see -

Dim C As Long 
Dim Cell As Range 
Dim NextRow As Long 
Dim Rng As Range 
Dim EntryWks As Worksheet 
Dim DBWks As Worksheet 
C = 1 
Set EntryWks = Worksheets("Sheet1") 
Set DBWks = Worksheets("Sheet2") 
Set Rng = EntryWks.Range("D2:F") 

NextRow = DBWks.UsedRange.Rows.Count 
NextRow = IIf(NextRow = 1, 1, NextRow + 1) 

For Each RA In Rng.Areas 
For Each Cell In RA 
C = C + 1 
DBWks.Cells(NextRow, C) = Cell 
Next Cell 
Next RA 
End Sub

[BAEdwards]

Many thanks for the reply. As far as I can tell the code you posted copies data from one worksheet to another. I am looking to populate a listbox using data from a worksheet. My code works in that it populates the list box with data; however it will only draw data from the active sheet and not Sheet2 as intended (Unless Sheet2 is the active sheet while the list box is being populated).

Many thanks,

Ben

[BAEdwards]

Think I have sussed it. This seems to work:

Dim queryrows As Integer
queryrows = Sheet2.UsedRange.Rows.Count

Dim WrkSheet As String
WrkSheet = Sheet2.Name

Me.PSFQueryResults.RowSource = WrkSheet & "!$D$2:$F$" & queryrows

I suspect that when setting the 'rng' Range variable no association is made to the sheet that the range originates from.

Many thanks,

Ben

[AndreRet]

Pleasure. Glad you could solve this. I'm sure someone will benefit from your solution. Please mark the thread as solved, thanks.