Search code examples
excelvbauserform

AddItem not populating options in combo box

I have the following form. I've added each item as per .AddItem "", however they are not populating in the combobox in the userform.

(This is in a separate module referring to the userform)

ttps://i.sstatic.net/kPIJq.png

Code

 With frmForm

    .txtFirst.Value = ""
    .txtLast.Value = ""
    .txtYear.Value = ""

    .cmbSchool.Clear
    .cmbSchool.AddItem "Harvard"
    .cmbSchool.AddItem "Northwestern"
    .cmbSchool.AddItem "UCBerkley"
    .cmbSchool.AddItem "Stanford"
    .cmbSchool.AddItem "NYU"
    .cmbSchool.AddItem "UoT"
    .cmbSchool.AddItem "UBC"
    .cmbSchool.AddItem "RMC"

    End With

Thanks!

Solution

  • Your issue is that within the UserForm code module you shouldn't reference the form's default name (think it as a blue print for further instances of this class), but the current object instance it is actually run - e.g. by prefixing controls with the Me. qualifier.

    This does assume the code is moved into the form's own code module - the Initialize handler would be a good place for this.

    Ways to populate a UserForm Combobox

    a) Populating via .AddItem method

        With Me.cmbSchool                       ' don't refer to the form's default instance
        .Clear
        .AddItem "Harvard"
        .AddItem "Northwestern"
        .AddItem "UCBerkley"
        .AddItem "Stanford"
        .AddItem "NYU"
        .AddItem "UoT"
        .AddItem "UBC"
        .AddItem "RMC"
    End With

    b) Populating via array

    Choosing this approach by assigning an array directly to the box'es .List property, you can shorten code to:

        Dim SchoolList As Variant
    SchoolList = "Harvard,Northwestern,UCBerkley,Stanford,NYU,UoT,UBC,RMC"
    Me.cmbSchool.List = Split(SchoolList, ",")

    Edit due to comment/2020-03-30

    "Can you define what you mean by instance?"

    A class - and a UserForm only represents a special type of class modules - can be regarded as sort of object template.

    A so called instance of the class object (based on all properties, methods and events provided by the UserForm|class) will be created at runtime as a ►New object with access to the "." properties , i.e. it's getting 'newed' (possibly repeatedly) to the current object you have declared and set to memory.

    If you prefer to call the procedure from a separate module (instead in the form's UserForm_Initialize() handler), you could "hold on to the object/instance (as long as you need it)" by formal object setting as shown below (alternatively: Dim myFrm As New frmForm).

    Example call in standard module 

    Sub ShowFormExample()
Dim myFrm    As frmForm   ' declare myFrm as object type belonging to your form's class
Set myFrm = New frmForm   ' set myFrm as new object instance to memory

FillItems myFrm           ' << call procedure FillItems
                  ' or integrate code here: Dim .../ SchoolList = ... / myFrm.cmbSchool.List = ...

myfrm.Show                ' form's .Show-method;  argument equals vbModal by default

End Sub

    Procedure FillItems

    Sub FillItems(myFrm As UserForm)
With myFrm
    Dim SchoolList As Variant
    SchoolList = "Harvard,Northwestern,UCBerkley,Stanford,NYU,UoT,UBC,RMC"
    .cmbSchool.List = Split(SchoolList, ",")
End With
End Sub

    Recommended readings