Set RowSource for ListBox

[Patrick C. Simonds]

I want to set the RowSource for a ListBox I have on a UserForm, to the range
A2:A20 on the worksheet named Storage Locations (this worksheet is in the
same workbook). I have been unsuccessful. Any help would be appreciated.

 

[dan dungan]

In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?

 

[dan dungan]

I found this from Tom Ogilvy in 2002:

With userform1
..Rowsource = Worksheets(1).Range("A1"). _
CurrentRegion.Resize(,3).Address(0,0,xlA1,True)
End With

 

[Patrick C. Simonds]

I tried entering:

Storage Locations'!$A$2:$A$20

The error I get is Invalid Property value. I am doing this in the
properties area of the ListBox.

 

[dan dungan]

Try removing the space in your sheet name

StorageLocations!a2:A20

 

[Dave Peterson]

You missed a leading apostrophe--or was that a typo in the post?

'Storage Locations'!$A$2:$A$20

And you're sure you have a sheet named "Storage Locations"?

 

[Gary Keramidas]

something like this should work:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Storage Locations")
Set rng = ws.Range("A1:a15")

Me.ListBox1.RowSource = rng.Address

End Sub

 

[Dave Peterson]

I'd use:

Me.ListBox1.RowSource = rng.Address(external:=true)

Just to make sure it was using the range from the correct sheet.

 

[Gary Keramidas]

good point, dave.

--

Gary
Excel 2003

I'd use:

Me.ListBox1.RowSource = rng.Address(external:=true)

Just to make sure it was using the range from the correct sheet.

 

[ShaneDevenshire]

Hi,

you could save yourself a lot of headaches if you just range name the data
source. Select the range A1:A20 or where ever the items are, and type a name
into the Name Box and press Enter. Suppose you named the range myList then
the row source line of the list/combo box would read myList. Range names
don't allow spaces.

Result: No quotes needed, no spreadsheet references needed, shorter name
less likely to produce a typo. And of course you can use the name in code if
you choose.
Range("myList").Select or [myList].Select for example.