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

 End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...