User Form Listbox filled from range on another sheet

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
I have this sub in a standard module but it could be in a userform module just the same.

Code:
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

Board Regular
Joined
Dec 30, 2010
Messages
61
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

Well-known Member
Joined
May 8, 2009
Messages
656
Instead of assuming you were going to pass anything I've moved the collection variable so it's dimensioned inside the sub now.

Code:
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

Board Regular
Joined
Dec 30, 2010
Messages
61
That still doesn't work. When I step through it I can see that

Code:
 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
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
 
Last edited:

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
Change

Code:
Dim Coll As Collection
with

Code:
Dim Coll As New Collection

That should fix it.

Also you may want to change

Code:
For i = FR + 1 To LR

to

Code:
For i = FR To LR

Better?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,765
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top