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.

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

What have you tried?
How did it fail?

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" wrote in message
...
In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?

Try removing the space in your sheet name

StorageLocations!a2:A20

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"?

"Patrick C. Simonds" wrote:

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" wrote in message
...
In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?

--

Dave Peterson

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

--


Gary


"Patrick C. Simonds" wrote in message
...
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" wrote in message
...
In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?

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 wrote:

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

--

Gary

"Patrick C. Simonds" wrote in message
...
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" wrote in message
...
In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?


--

Dave Peterson

good point, dave.

--

Gary
Excel 2003


"Dave Peterson" wrote in message
...
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 wrote:

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

--

Gary

"Patrick C. Simonds" wrote in message
...
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" wrote in message
...
In Excel 2000, I just typed a2:a20 in the listbox properties
rowsource.

What have you tried?
How did it fail?


--

Dave Peterson

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

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.

--
Thanks,
Shane Devenshire


"Patrick C. Simonds" wrote:

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.