Setting Rowsource for Listbox

**jomili** · 09-30-2010, 10:09 AM

I'm having the most awful time trying to set up my rowsource for my Listbox, and would appreciate some help.

My UserForm has a name, "SelectDate"
My listbox in the userform has a name, "Listbox1"
In the Properties section of "Listbox1" I've left the "Rowsource" blank

My data to populate the listbox is in the current workbook, in a sheet called "Steps", in column G. This data is in Dynamic Named Range. The name is "Dates", the "refers to" section says "=OFFSET(Steps!G1,0,0,COUNTA(Steps!$G:$G),1)"

I have the following code in place:

Please Login or Register  to view this content.

I've tried it with and without keying "SelectDate". Whichever way I go when I run the code I get a Run-time error 13, Type Mismatch.

What am I doing wrong?

I would appreciate any help I can get.

**romperstomper** · 09-30-2010, 10:11 AM

Rowsource takes an address string, not a range. I suggest you don't use it though - assign the value of the range to the List property of the listbox instead.

**jomili** · 09-30-2010, 10:14 AM

When I look at the properties on the Listbox I don't see "List", only "ListStyle". I've tried using the "RowSource", but can't get that to behave either.

**royUK** · 09-30-2010, 10:15 AM

Try

Please Login or Register  to view this content.

**jomili** · 09-30-2010, 10:18 AM

Roy,
That got us past the runtime error, but my userform comes up with an empty Listbox.

**romperstomper** · 09-30-2010, 10:21 AM

Then there is no data in the range.
To use the rowsource (but don't!) you would use:

Please Login or Register  to view this content.

**jomili** · 09-30-2010, 10:22 AM

I decided it might help to have an example, so I've attached one.

**romperstomper** · 09-30-2010, 10:40 AM

Your range name is defined incorrectly. The B2 reference needs to be $B$2

**jomili** · 09-30-2010, 10:47 AM

I knew it had to be something simple. I don't know how my value changed from G1 to B1, but I did not have my $ in place, so I guess it would change. I at that about a hundred times, and never saw that piece. Thanks so much!

**romperstomper** · 09-30-2010, 11:21 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save