userform listbox

[Zrx]

Hello folks,

I'm lost as to how I would create the "list" in a listbox on a userform.

Wk1_Jan_07

Wk2_Jan_07

Wk3_Jan_07

Wk4_Jan_07

Wk1_Feb_07

Wk1_Feb_07

And so on is my list content. Oh yes I forgot. The above names represent (ranges names) in the worksheet (1)

Any thoughts?

Ralph

[davesexcel]

Try this code:

Please Login or Register  to view this content.

when you show the userform the listbox populates from your range


if you want to add items manually then you would use this code

Please Login or Register  to view this content.

[lrhodes]

shoot this is what I needed in my question, thanx
I just wish I had seen it before I posted that thread.

[Zrx]

This is confusing me somewhat, sorry.

ListItems = Range("yourRangeName").Value

Does this mean I need to have my "range names" in a "range" itself as to pick up for listbox??

I have 52 range names for the weeks of the year. Example Wk1_Jan_07,Wk2_Jan_07 and so on. These are names of ranges 15 rows high 12 columns wide.

The listbox will allow you to choose a week then I assume case statement will allow choice to be run afterwords (working on this code therory).

Also, this line in second sub "Private Sub UserForm1_Initialize()" gives me an ambiguous name detected: userform1_initialize error?

[heinousanus]

Thankyou for this!

I created a list on the spreadsheet: A1 = a, A2 = b, A3 = c etc.
Selected it
Clicked on Insert menu > Name > Define...
And in the Names in Workbook option I typed: yourRangeName

Then I used Alt + F11 to get into the Visual Basic Editor
Insert menu > UserForm
On the small Toolbox I selected Listbox and moved the mouse over the Userform
I pressed down the left mouse button and dragged downwards and to the right. This created a Listbox.
Pressed F7 to view the UserForm code
pasted in your code

Then I pressed F5 to run the userform and it worked! Cheers - will be very useful.

[davesexcel]

Here is microsofts help support site that shows how to populate a listbox using multiple ranges

http://support.microsoft.com/?kbid=161534

[Zrx]

Thanks Dave,

Beacuse of this line in the help file

"The items in the ranges A1:A3 and C1:C3 on Sheet1 are read into an array and are then assigned to the list of ListBox1. "

I will need to rethink my plan.

As I said I don't need the "items" in the "range name" just want to use the range name as a "string" I guess so the case statements can pick from there and then the case will do the work for that picked "string".

Ralph

[davesexcel]

Okay, I think I am getting this....
In your userform, you want to select a range from the list box for your calculations...........


Or do you want to select a range from a list box and that selection populates another listbox??

Thanks Dave,

Beacuse of this line in the help file

"The items in the ranges A1:A3 and C1:C3 on Sheet1 are read into an array and are then assigned to the list of ListBox1. "

I will need to rethink my plan.

As I said I don't need the "items" in the "range name" just want to use the range name as a "string" I guess so the case statements can pick from there and then the case will do the work for that picked "string".

Ralph