Excel VBA - How do I populate the values of a ListBox from a variable range?

Question

I have a list of names in Column A in a worksheet named "Email"

I want to populate a userform ListBox with the names Column A. However, I can't specify a fixed range as this list will grown and shrink. So how do I get the userform to populate the list with the correct number of items?

This is what I am currently trying but is not working (I'm sure it will be obvious to some people on here as to why not), I also saw another example using a simple For loop but I am unable to find the example again to show you.

Private Sub UserForm_Initialize()

Dim rngName As Range
Dim rng1 As Range
Dim rng2 As Range
Dim ws As Worksheet


Set ws = Worksheets("Email")
Set rngName = ws.Range("A:A").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set rng1 = ws.Range("A1")

On Error GoTo ErrorHandle

Me.lbUsed.List = Range(rng1 & ":" & rngName).Value

ErrorHandle:

End Sub

EDIT:

I now have the following code but it is failing to work when I load the userform:

Private Sub UserForm_Initialize()

Dim rngName As Range
Dim rng1 As Range

Set rngName = Worksheets("Email").Range("A:A").Cells.Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Set rng1 = Worksheets("Email").Range("A1:" & rngName.Address)

Me.lbUsed.List = Worksheets("Email").Range(rng1).Value


End Sub

Can anyone point me in the correct direction?

Netloh · Accepted Answer · 2014-02-25T12:39:54.653

5

If you want to populate your listbox with all of the items in column A (assuming that these are in a continuous range), you could do this simply by modifying you code like this:

Private Sub UserForm_Initialize()
    Dim rngName As Range
    Dim ws As Worksheet
    Dim i As Integer

    Set ws = Worksheets("Email")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then Me.lbUsed.AddItem ws.Cells(i, 1).Value
    Next i
End Sub

edited Feb 25 '14 at 12:39

answered Feb 24 '14 at 20:06

Netloh

4,243
4
23
37

using `.End(xlDown)` is very unreliable. What would be if `A1` is empty? this may be interesting: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Dmitry Pavliv Feb 24 '14 at 20:07
That did the trick perfectly! Can you explain this part please?: Set rngName = ws.Range("A1", ws.Range("A1").End(xlDown)) – Petay87 Feb 24 '14 at 20:08
@simoco That is true, but that is why I added the part about it being a `continuous range`. – Netloh Feb 24 '14 at 20:08
It would always be a continuous range with no gaps so that works fine in this example. – Petay87 Feb 24 '14 at 20:10
1

@Petay87 It works like the keyboard shortcut ctrl+shift+down when you are standing in cell `A1`. So it states the range from A1 and downward. But as @simoco correctly points out, this only works as expected on a continuos range. – Netloh Feb 24 '14 at 20:13
Ok so this no longer works as there have been items removed from the range and it is now populating blank items. – Petay87 Feb 25 '14 at 12:14
1

@Petay87 I have updated the code so that it will only add items from non-blank cells. – Netloh Feb 25 '14 at 12:41
@SørenHoltenHansen That now works exactly as I want! – Petay87 Feb 25 '14 at 12:44

score 2 · Answer 2 · answered May 19 '14 at 18:53

Point the RowSource property of the ListBox to dynamic Named Range in your spreadsheet. As you add or remove items to the range, the list will automatically pull in new items to the listbox. There's no need to write any code implement this requirement.

Excel VBA - How do I populate the values of a ListBox from a variable range?

EDIT:

2 Answers2