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?

Private Sub Worksheet_Activate()
UserForm9.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
UserForm9.Hide
End Sub

Private Sub UserForm_Initialize()
    Dim Cel As Range
    Dim Test As Boolean
    Test = False
    
    For Each Cel In Range("TListDNR")
        If Cel.Value = 12 Then Test = True
    Next Cel
    
    If Test = True Then
        UserForm9.ListBox1.RowSource = "=NewListDNR"
    End If

End Sub

[Zimmerman]

By the way. what you did was impressive

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

[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.

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    With ListBox1
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem can be set at design time
        
        For Each oneCell In Sheet1.Columns("T").SpecialCells(xlCellTypeConstants)
            If Val(oneCell.Value) = 12 Then
                .AddItem oneCell.Offset(0, -2).Value
                .List(.ListCount - 1, 1) = oneCell.Offset(0, -2).Address(, , , True)
            End If
        Next oneCell
    End With
End Sub


Private Sub ListBox1_Click()
    With ListBox1
        If .ListIndex <> -1 Then
            Label1.Caption = .List(.ListIndex, 0) & vbCr & "(from " & .List(.ListIndex, 1) & ")" & vbCr & "chosen."
        Else
            Label1.Caption = "nothing chosen"
        End If
    End With
End Sub

[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.

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    With ListBox1
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem can be set at design time
        
        For Each oneCell In Sheet1.Columns("T").SpecialCells(xlCellTypeConstants)
            If Val(oneCell.Value) = 12 Then
                .AddItem oneCell.Offset(0, -2).Value
                .List(.ListCount - 1, 1) = oneCell.Offset(0, -2).Address(, , , True)
            End If
        Next oneCell
    End With
       With ListBox1
        If .ListIndex <> -1 Then
            Label1.Caption = .List(.ListIndex, 0) & vbCr & "(from " & .List(.ListIndex, 1) & ")" & vbCr & "chosen."
        Else
            Label1.Caption = "nothing chosen"
        End If
        End With
End Sub

[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

With ListBox1
    If .ListIndex <> -1 Then
        Label1.Caption = .List(.ListIndex, 0) & vbCr & "(from " & .List(.ListIndex, 1) & ")" & vbCr & "chosen."
    Else
        Label1.Caption = "nothing chosen"
    End If
End With

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

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    Dim RangeOfCells as Range

    With Sheet1.Range("T:T")
        Set RangeOfCells = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlup))
    End With

    With ListBox1
        .Clear
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem can be set at design time
        
        For Each oneCell In RangeOfCells
            If Val(oneCell.Value) = 12 Then
                .AddItem oneCell.Offset(0, -2).Value
                .List(.ListCount - 1, 1) = oneCell.Offset(0, -2).Address(, , , True)
            End If
        Next oneCell
    End With
End Sub

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,

ListBox1.RemoveItem "Janet Smith"

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

Private Sub TextBox1_Change()
    Dim Lines As Variant
    Lines = Split(TextBox1.Text, vbCr)
    With ThisWorkbook.Sheets("Sheet1").Range("A1:A20")
        .ClearContents
        .Resize(Ubound(Lines, 1), 1).Value = Application.Transpose(Lines)
    End With
End Sub

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.