User Form Listbox filled from range on another sheet

[mmetzinger]

OK, so I have enrollments.xls which has a column, BA. that has multiple company names. I need a user form to open with a listbox that populates itself from column BA. I know this is probably simple but I can't figure out where the code goes to even start trying to work my way through it. Should the fill commands be in the userform or the regular workbook?

Also, column BA will have the same company listed multiple times. Is there a way that the listbox will only show the company name once?

 

[craig.penny]

I have this sub in a standard module but it could be in a userform module just the same.

Sub stuff(ByRef Coll As Collection, LB as ListBox)
      Dim i As Integer
      Dim FR As Integer: FR = 1
      Dim LR As Integer: LR = 10
      Dim C As Integer: C = 1
      Dim s As Worksheet: Set s = ActiveWorkbook.Sheets("Sheet1")
      
      On Error Resume Next
      
      For i = FR + 1 To LR
            Coll.Add s.Cells(i, C), CStr(s.Cells(i, C))
      Next i
      
      LB.Clear
      For i = 1 To Coll.Count
            LB.AddItem Coll(i)
      Next i
      
      On Error GoTo 0
End Sub

When I use it I actually send all the variables. I just thought it would be easier for you to read and understand if I dimmed them here.

Doing it this way will eliminate duplicates in your collection.

 

[mmetzinger]

OK, so that doesn't seem to work. I have the code in userform but I can't get it to set the source to be my other workbook. The workbook name is "np numbers.xlsx" which needs to be set for the S variable. Also, my listbox is called listbox1 not LB but easy enough to change. I can't figure out how to set the workbook value for S correctly and the values I need are in column "BA". Can you change your code to reflect this and send it to me?

Thanks,
The eternal vba noob

 

[craig.penny]

Instead of assuming you were going to pass anything I've moved the collection variable so it's dimensioned inside the sub now.

Sub stuff()
      Dim i As Integer
      Dim Coll As Collection
      Dim FR As Integer: FR = 1 'First row of data, change as necessary
      Dim LR As Integer: LR = 10 'Last row of data, change as necessary
      Dim C As Integer: C = 53 'column of data.  "BA" is column 53
      Dim LB As ListBox
      Dim w As Workbook: Set w = Workbooks("np numbers.xlsx")
      Dim s As Worksheet: Set s = w.Sheets("Sheet1") 'change Sheet1 to your sheet name
      
      On Error Resume Next
      
      For i = FR + 1 To LR
            Coll.Add s.Cells(i, C), CStr(s.Cells(i, C))
      Next i
      
      LB.Clear
      For i = 1 To Coll.Count
            LB.AddItem Coll(i)
      Next i
      
      On Error GoTo 0
End Sub

Does that make sense?

 

[mmetzinger]

That still doesn't work. When I step through it I can see that

 For i = FR + 1 To LR
Coll.Add s.Cells(i, C), CStr(s.Cells(i, C))
Next i

is running through all the values in that column but when I watch the collection variable is constantly says "nothing" for a value.

After the whole thing runs the listbox isn't populated.

For note the only changes I made to your code to mine was:
LR= 3000
removed the "Dim LB as listbox"
replace LB in the code with listbox1

My Code

      Dim i As Integer
      Dim Coll As Collection
      Dim FR As Integer: FR = 1 'First row of data, change as necessary
      Dim LR As Integer: LR = 3000 'Last row of data, change as necessary
      Dim C As Integer: C = 53 'column of data.  "BA" is column 53
      Dim w As Workbook: Set w = Workbooks("NP numbers.xlsx")
      Dim s As Worksheet: Set s = w.Sheets("Sheet1") 'change Sheet1 to your sheet name
      
      On Error Resume Next
      
      For i = FR + 1 To LR
            Coll.Add s.Cells(i, C), CStr(s.Cells(i, C))
      Next i
      
    For i = 1 To Coll.count
    ListBox1.AddItem Coll(i)
    Next i

    On Error GoTo 0

 

[craig.penny]

Change

Dim Coll As Collection

with

Dim Coll As New Collection

That should fix it.

Also you may want to change

For i = FR + 1 To LR

to

For i = FR To LR

Better?