vbaexcellistboxlistboxitem

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


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?


Solution

  • 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