USERFORMS: Populating multiple comboboxes with the same data

[liampog]

The following code is located in the Userform_Initialise part. DayPB, MidPB and NightPB are all comboboxes that are required to display the same data from the spreadsheet.

With Me.DayPB
        For PBNames = 132 To 141
            If Range("B" & PBNames).Value <> "" Then
            .AddItem Range("B" & PBNames)
            Else: Exit For
            End If
        Next PBNames
        .ListIndex = 0
    End With
    
    With Me.MidPB
        For PBNames = 132 To 141
            If Range("B" & PBNames).Value <> "" Then
            .AddItem Range("B" & PBNames)
            Else: Exit For
            End If
        Next PBNames
        .ListIndex = 0
    End With
    
    With Me.NightPB
        For PBNames = 132 To 141
            If Range("B" & PBNames).Value <> "" Then
            .AddItem Range("B" & PBNames)
            Else: Exit For
            End If
        Next PBNames
        .ListIndex = 0
    End With

This is the code I currently use. Is there any way of simplifying it?

I tried this, but to no avail:

Call PBChoice(DayPB)
Call PBChoice(MidPB)
Call PBChoice(NightPB)



Sub PBChoice(PB As String)

    With Me.PB
            For PBNames = 132 To 141
            If Range("B" & PBNames).Value <> "" Then
            .AddItem Range("B" & PBNames)
            Else: Exit For
            End If
        Next PBNames
        .ListIndex = 0
    End With
    
End Sub

Can anyone tell me the solution?

 

[Krishnakumar]

Hi,

Try

Dim vList() As String, n As Long
Dim PBNameList, i As Long

PBNameList = Range("b132:b141")

For i = 1 To UBound(PBNameList, 1)
    If Len(PBNameList(i, 1)) Then
        n = n + 1
        ReDim Preserve vList(1 To n)
        vList(n) = PBNameList(i, 1)
    End If
Next

If n Then
    Me.DayPB.List = vList
    Me.MidPB.List = vList
    Me.NightPB.List = vList
End If

HTH