How to have a userform list box fill automaytically with cell values

[Zimmerman]

I have a userform that I would like to auto populate a list box. The value's I want to populate this with are in Sheet3 cells R4:R300. But what I need to do is look at column T and if any value in column T is 12 then the value in column R is what will fill this list box. There could be up to 30 items that would be in this list box. List box is called ListBox 1

Thanks in advance
Excel 2010

[MarvinP]

Hi Zimmerman...

You should update your profile to say either Excel 2003 or 2010 or both....

Here is an example that should get close to what you are asking.

I've created a Userform that has a single listbox in it. The ListBox.RowSource is what you need to change. I've done some Dynamic Named Ranges to keep it easier. You find the ListBox properties by clicking on the ListBox and showing (view) Properties.

If you look at the code behind the Userform, I've added some code to check the RList to see if it has a 12 in it. If it does I show a different RowSource.

I hope this example gets you closer to solving your problem. Lots to learn here if it doesn't make sense.

[Zimmerman]

That seems to work out perfect. Only issue that I'm having is I have this code in it. So when I change sheets to go to sheet 3 it gives me an error that says row source is out of range. Any ideas on what I can do?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Zimmerman]

By the way. what you did was impressive

[mikerickson]

RowSource isn't supported by my Mac, so I came up with this.
Note that the second (hidden) column of the list box retains the address of the cell from which the list item came.

Please Login or Register  to view this content.

[MarvinP]

What happens if you remove the "vbModeless" ? Does it work then?

Did you see and copy the Dynamic Named Ranges to your working version?

[Zimmerman]

Sorry for the delay. It was getting late last night. Here's what I have right now. And when I change to sheet 3 the userform with the list box show up like it's supposed to and whne I change to another sheet it goes away like it's supposed to. Now my problem is the list box is pulling every cell value in column R. It's not recognizing if there's a 12 in column T to only pull that value in column R.

Please Login or Register  to view this content.

[mikerickson]

Can you attach a sample workbook of your situation?
The code explicitly asks that column T be 12 before adding any item to the ListBox.
I suspect that the cell locations need to be checked.
Also, my code assumes that these 12's are constants and not the result of a formula evaluation.

Finally, the code

Please Login or Register  to view this content.

was placed as a demonstration of how to retrieve the selected item's source cell address. It is not necessary and it does not go in the Userform_Intialize event, but in the ListBox1_Change event.

[Zimmerman]

Sorry for asking this but how do I attach the file?

[Zimmerman]

Yes the result in column T is a formula. It's subtracting years. If I could attach my fie you'd probably see an easier way to accomplish what I'm trying to do.

[mikerickson]

Try this

Please Login or Register  to view this content.

But if you are going to different sheets, hiding the user form, changing cells which change column T and going back to look at the user form, that code should be moved to the Activate event rather than the Intialize event.

[Zimmerman]

That seemed to work Thank you very much. One more question though if you don't mind. This workbook is for a little league baseball draft. This list shows all the available 12 year olds. If a 12 year old gets moved from the list to a team is there a way to have this userform update and now that kid will be removed from the listbox on the userform.

[mikerickson]

Yes there is a way,

Please Login or Register  to view this content.

But when to trigger that line would depend on how Janet gets "chosen"

[Zimmerman]

Ideally it would be nice to have the user pick the name from the list. Like click on the name and drag it over to whatever team picked them.

[mikerickson]

This might give you some ideas.
Select a player from the list box and drag the box to the team of your choice.

[Zimmerman]

ok that's awesome. I'll play around with this. But this is great

[Zimmerman]

Question for you though. Can I drag names from the list n the userform to cells in a worksheet rather than dragging names from the userform to another listbox on that same userform like you have? As it stands right now on yours you can drag names over to a team that's listed on the userform. I would love to have my userform show the list of kids when that sheet is active (as it does now) but then the user can pick from that userform list and drag that name to a cell on the sheet

[mikerickson]

You are working with a modeless user form that my Mac won't support.
My guess is: No, you can't drag it to the worksheet, but you could set a control to automatically reflect its changes in a worksheet.

What I posted in Post#15 uses Labels to accept the player's names (I started with ListBoxes but discovered that they are always in front which gave a bad visual effect, so changed to Labels.)

It should be easy enough to change that to TextBoxes holding the team rosters and those text boxes being linked to cells.

Something like

Please Login or Register  to view this content.

Perhaps also paired with a Worksheet_Change event to fill the TextBox when the cells in the worksheet are changed.
As i said, I can't test a modeless user form.