USERFORMS/Comboboxes

[liampog]

Hi there

I'm using a formula function to create a value called vList, which is a list of names.

After the list has been created, I then want to assign the same list to a number of comboboxes. Is there any way of shortening the code that follows:

If UBound(NameList, 1) > 0 Then
        Me.DealerShiftTime1.List = vList
        Me.DealerShiftTime2.List = vList
        Me.DealerShiftTime3.List = vList
        Me.DealerShiftTime4.List = vList
        Me.DealerShiftTime5.List = vList
        Me.DealerShiftTime6.List = vList
        Me.DealerShiftTime7.List = vList
        Me.DealerShiftTime8.List = vList
        Me.DealerShiftTime9.List = vList
        Me.DealerShiftTime10.List = vList
        Me.DealerShiftTime11.List = vList
        Me.DealerShiftTime12.List = vList
        Me.DealerShiftTime13.List = vList
        Me.DealerShiftTime14.List = vList
        Me.DealerShiftTime15.List = vList
        Me.DealerShiftTime16.List = vList
        Me.DealerShiftTime17.List = vList
        Me.DealerShiftTime18.List = vList
        Me.DealerShiftTime19.List = vList
        Me.DealerShiftTime20.List = vList
        Me.DealerShiftTime21.List = vList
        Me.DealerShiftTime22.List = vList
        Me.DealerShiftTime23.List = vList
        Me.DealerShiftTime24.List = vList
        Me.DealerShiftTime25.List = vList
        Me.DealerShiftTime26.List = vList
        Me.DealerShiftTime27.List = vList
        Me.DealerShiftTime28.List = vList
        Me.DealerShiftTime29.List = vList
        Me.DealerShiftTime30.List = vList
        Me.DealerShiftTime31.List = vList
        Me.DealerShiftTime32.List = vList
        Me.DealerShiftTime33.List = vList
        Me.DealerShiftTime34.List = vList
        Me.DealerShiftTime35.List = vList
        Me.DealerShiftTime36.List = vList
        Me.DealerShiftTime37.List = vList
        Me.DealerShiftTime38.List = vList
        Me.DealerShiftTime39.List = vList
        Me.DealerShiftTime40.List = vList
    End If

I tried using a For/Next loop for 1 to 40, but it didn't work.

Is it possible?

 

[doofusboy]

Maybe something like this?

Dim ctl As Control
    For Each ctl In Controls
        If Mid(ctl.Name, 1, 15) = "DealerShiftTime" Then
            ctl.List = vList
        End If
    Next ctl

 

[texasalynn]

here is a sample of of looping for list box

Sub MyListBox()
Dim lb As ListBox
Dim i As Long, temp

Set lb = Sheets("Sheet1").ListBoxes(1)

With lb
    For i = 1 To .ListCount
        If .Selected(i) Then temp = temp & i & " " & .List(i) & Chr(10)
    Next i
End With

MsgBox "Selected items were:" & Chr(10) & temp
    
End Sub

 

[GTO]

...I tried using a For/Next loop for 1 to 40, but it didn't work.

Is it possible?

Yes. As you are using a consistent naming convention and a number, try like:

Private Sub UserForm_Initialize()
Dim i As Long
Dim ary
    
    ary = Array("VAL01", "VAL02", "VAL03", "VAL04", "VAL05", "VAL06", "VAL07", "VAL08", "VAL09", "VAL10")
    For i = 1 To 5
        Me.Controls("ComboBox" & i).List = ary
    Next
End Sub

Does that help?

Mark