Pages

Saturday, February 22, 2014

Loop through all List-Boxes or Combo-boxes on userform


Macro to loop through all combo-boxes and list-boxes on user-form and set the row-source



 Sub loop_listbox_userform()
    Dim cnt As Control
    ' change userform name
    For Each cnt In UserForm1.Controls ' loop through all controls on userform
        If TypeName(cnt) = "ListBox" Then ' Set rowsource for listbox
           cnt.RowSource = "Sheet1!a1:a7"
        ElseIf TypeName(cnt) = "ComboBox" Then 'Set rowsource for combobox
           cnt.RowSource = "Sheet1!b1:b7"
        End If

    Next
    UserForm1.Show
 End Sub

No comments:

Post a Comment