How to set ListBox1.RowSource property?

Question

I have an Excel workbook with 16 sheets.

I want a listbox on my userform which will list the data in Sheet 1 Cells F2 to F50.

My code

Private Sub UserForm_Initialize()

ListBox1.Clear

Sheet11.Activate

ListBox1.RowSource = "Sheet11!F2:F10"

End Sub

It returns error

"Run Time Error '380' Could Not set the RowSource property. Invalid Property Value"

I tend to use tables. Then you can do something like this to add the values from table after you have cleared listbox: Me.oMyListBox.List = oMyWB.oMyWS.ListObjects("<table name>").DataBodyRange.Value — Zac
using "Sheet11!F2:F10" is looking for a sheet named "Sheet11" using Sheet11.Activate is actually activating the 11th sheet. You should be using the sheet name not the sheet index. As the row source. You do not need to activate the sheet to populate the listbox, unless you really want to. — Davesexcel
Thank you Davesexcel, it works when I use the Sheet Name instead of the Sheet number (Sheet 11, Sheet 10, etc). I thought the object for Excel VBA usually follows the name listed in the properties of the object when you look at it on Developer parts. — Adrian Hartanto

Domenic Domenic · Accepted Answer · 2018-09-17T11:52:33

To refer to the sheet name, assuming your sheet is named Sheet1...

ListBox1.RowSource = "'Sheet1'!F2:F10"

To refer the code name for the sheet, assuming that Sheet11 is the code name...

ListBox1.RowSource = "'" & Sheet11.Name & "'!F2:F10"