How do I populate a listbox in Excel Userform with data from the non active sheet

[keithaul]

I know there are various ways to populate a listbox in an excel 2016 userform with data from a specific sheet in my workbook. However his is my problem

If I have the following code in my initialize event of the user form, I want to display the data from the specific sheet I specify in my VBA code, not the data from the currently selected sheet in my workbook. Whenever I have a different sheet selected in my workbook, other than the sheet named in the VBA code, it always pulls the data from the current selected sheet. the following code only works when the sheet named in the code equals the current selected sheet in the workbook

so how can i change the following code so it always pulls data from the sheet named in the code, regardless of what sheet is selected in the workbook?

Dim rSource As Range
Dim LastRow As Integer, LastCol As Integer

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sold Items ListObjects")


'If the source range is in a table named "SoldItemsListObj" on Sheet "Sold Items ListObjects":
' ListObjects - represents a list of tables in the worksheet
' DataBodyRange - represents the table data, excluding the header

' List Property of a Listbox holds the items/list in the list box
lbSoldItems3.List = Worksheets("Sold Items ListObjects").ListObjects("SoldItemsListObj").DataBodyRange.Value

'Lets get the last row that has data in it.
' start at last row in the worksheet, 1st column;
' press the end key + the up arrow key will bring you to the last row filled with data


LastRow = Worksheets("Sold Items ListObjects").Cells(Rows.Count, 1).End(xlUp).Row

'Lets get the last column that has data in it.
' start at row 1, last column in worksheet;
' press the end key + the left arrow key will bring you to the last column with data in it

LastCol = Worksheets("Sold Items ListObjects").Cells(1, Columns.Count).End(xlToLeft).Column

With Worksheets("Sold Items ListObjects")
'set the range to - start in row 2, column one and end at last row with data and last column with data
Set rSource = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With

With frmListObjects.lbSoldItems3
.RowSource = rSource.Address 'return the address range of the data you want to display in the listbox
.ColumnHeads = True 'display the column headings
.ColumnCount = .ColumnHeads '# of columns you want displayed
End With

[Kenneth Hobs]

Please paste code between code tags. Type them or click the # icon on the message's toolbar.

.RowSource = rSource.Address(External:=True) 'return the address range of the data you want to display in the listbox

[keithaul]

Ok. Your solution works. But I don't understand why it works. I'm not understanding why I have to use this option with the .Address property. I don't see a explanation on the net that I understand.

[Kenneth Hobs]

IT is mostly the same as normal cell referencing. Type = in a formula and click A1 in the current sheet. =A1 is then the formula. Do the same but then click A1 in another sheet. Now you have something like =Sheet2!A1. As for the External option in Address() simply press F1 with cursor next to or in the command word like Address().

[keithaul]

Let me rephrase what am I asking

I'm setting rsource below with this line of code that's specifically points to the worksheet that I want the data come from.

With Worksheets("Sold Items ListObjects")
'set the range to - start in row 2, column one and end at last row with data and last column with data
Set rSource = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With

So I have to ask again why do I need this external option when I'm specifically hard-coding what worksheet I want the data to come from?

[Kenneth Hobs]

Again, it is all about knowing what options the Range object's Address property offers. Did you not view the help as I explained? https://msdn.microsoft.com/en-us/library/office/ff837625(v=office.15).aspx

It shows how to use MsgBox to view the address string with various parameter options. Debug.Print is a good way to see the result in the Immediate window after a run too.

Again, please paste code between code tags. Type them or insert them by clicking # icon on the toolbar. e.g.

With Worksheets("Sold Items ListObjects")
'set the range to - start in row 2, column one and end at last row with data and last column with data
Set rSource = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With
MsgBox rSource.Address
MsgBox rSource.Address(External:=True)

[jolivanes]

Would this not do?

Private Sub UserForm_Initialize()
ListBox1.List = Sheets("Sheet2").Range("a1:a" & Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

[Kenneth Hobs]

I normally use the .List method as well jolivanes. The OP knew how to do that so I did not mention it.