Search This Blog

Saturday 11 August 2012

ADD ITEMS TO ALL COMBOBOXES OF A USERFORM AT ONE TIME

This is required when you have a number of comboboxes on a userform and you need to add the same list of items to all comboboxes. Instead of adding items one by one to each combobox, you can just use the below code.

Paste this code on userform_intialize event and run.


Private Sub UserForm_Initialize()
Dim nme As Range
  Dim cntrl As Control
  Dim CB As ComboBox
  For Each cntrl In Me.Controls
    If TypeName(cntrl) = "ComboBox" Then
      If CB Is Nothing Then
        For Each nme In Sheet1.Range("MyName")
          cntrl.AddItem nme.Value
          Set CB = cntrl
        Next
      Else
        cntrl.List = CB.List
      End If
    End If
  Next
End Sub

Change highlighted part as per your requirement

No comments:

Post a Comment