CODEWITHSUNDEEP
vbaexcellistboxuserform
SB999
SB999

Reputation: 307

For each or Named Range to populate ListBox

All I am trying to populate a listbox with a For Each loop which iterates through the rows. The for each loop is going through the items in a Named range (ProgramIDs).

The current code I am using is 

If Len(ProjectInformation.Range("H2").Value) = 7 Then

    Dim Lr As Long
    Lr = Range("H1048576").End(xlUp).Row
    For Each C In Range("H2:H" & Lr)
        With Program_ListBox
            .AddItem C.Value
        End With
    Next C

End If

I fear this is a very basic question however after researching the website / google I simply cannot get this simple task to function.

Any help would be appreciated.

Upvotes: 0

Views: 1230

Answers (2)

Yuca
Yuca

Reputation: 6091

There is no need to loop, you can pass the range as the source of the listbox

Program_ListBox.List = Range("H2:H" & Lr)

Upvotes: 1

user9298223
user9298223

Reputation:

Range("H2:H" & Lr) references the cells on the ActiveSheet. You should always fully qualify your references.

With ProjectInformation
    If Len(.Range("H2").Value) = 7 Then
        For Each C In .Range("H2", .Range("H" & .Rows.Count).End(xlUp))
            With Program_ListBox
                .AddItem C.Value
            End With
        Next C
    End If
End With

There is no need loop the cells to the add the values to the listbox. You can assign the Range().Value array directly to the Listbox.List array.

 With ProjectInformation
    If Len(.Range("H2").Value) = 7 Then
        Program_ListBox.List = .Range("H2", .Range("H" & .Rows.Count).End(xlUp)).Value
    End If
End With

Upvotes: 0

Related Questions