Search code examples
excelvbacomboboxuserform

Excel VBA UserForm ComboBox not displaying named range list

I am trying to populate a combo box with a named range "rngEnclosure" but it isn't populating the ComboBox, but there are no error messages. The strange thing is that I have another ComboBox populating with that same list and it works.

Broken ComboBox

1

Working ComboBox

2

This line doesn't populate the ComboBox:

'Fill primary enclosure material combo box
    cbPrimeEnclosureMat.List = Application.WorksheetFunction.Transpose(Range("rngEnclosure"))

But this line does:

'Fill secondary enclosure material combo box
    cbSecEnclosureMat.List = Application.WorksheetFunction.Transpose(Range("rngEnclosure"))

Full code for the UserForm Initialize event:

Private Sub UserForm_Initialize()
'POPULATE FIELDS UPON USERFORM INITIALIZATION
'PROJECT INFORMATION
'Empty project name text box
    txbProjectName.Value = ""

'Empty project type combo box
    cbProjectType.Clear

'Fill project type combo box
    cbProjectType.List = Application.WorksheetFunction.Transpose(Range("rngProjectType"))

'Empty reporting combo box
    cbReported.Clear

'Fill reporting combo box
    cbReported.List = Array("Yes", "No")

'Empty GSF text box
    txbGSF.Value = ""
    
'Empty number of floors text box
    txbFloors.Value = ""
    
'Empty F2F text box
    txbF2F.Value = ""

'Empty footprint shape combo box
    cbFootprint.Clear

'Fill footprint shape combo box
    cbFootprint.List = Application.WorksheetFunction.Transpose(Range("rngFootprint"))

'Empty articulation combo box
    cbArticulation.Clear

'Fill articulation combo box
    cbArticulation.List = Application.WorksheetFunction.Transpose(Range("rngArticulation"))
    
'FOUNDATION AND PODIUM
'Empty foundation type combo box
    cbFoundation.Clear

'Fill project type combo box
    cbFoundation.List = Application.WorksheetFunction.Transpose(Range("rngFoundation"))

'Empty SOG thickness text box
    txbSOGThickness.Value = ""

'Empty number of piles text box
    txbPiles.Value = ""
    
'Empty pile depth text box
    txbPileDepth.Value = ""

'Empty foundation type combo box
    cbPodium.Clear

'Fill project type combo box
    cbPodium.List = Array("Yes", "No")

'Empty number of podium floors text box
    txbPodiumFloors.Value = ""

'STRUCTURE
'Empty floor slab thickness text box
    txbFloorSlabThickness.Value = ""

'Empty floor slab material combo box
    cbFloorSlabMat.Clear

'Fill floor slab material combo box
    cbFloorSlabMat.List = Application.WorksheetFunction.Transpose(Range("rngFloorSlab"))

'Empty primary structure % text box
    txbPrimeStructurePerc.Value = ""

'Empty primary structure material combo box
    cbPrimeStructureMat.Clear

'Fill primary structure material combo box
    cbPrimeStructureMat.List = Application.WorksheetFunction.Transpose(Range("rngStructure"))

'Empty secondary structure % text box
    txbSecStructurePerc.Value = ""

'Empty secondary structure material combo box
    cbSecStructureMat.Clear

'Fill secondary structure material combo box
    cbSecStructureMat.List = Application.WorksheetFunction.Transpose(Range("rngStructure"))

'ENCLOSURE
'Empty primary enclosure % text box
    txbPrimeEnclosurePerc.Value = ""

'Empty primary enclosure material combo box
    cbPrimeEnclosureMat.Clear

'Fill primary enclosure material combo box
    cbPrimeEnclosureMat.List = Application.WorksheetFunction.Transpose(Range("rngEnclosure"))

'Empty secondary enclosure % text box
    txbSecEnclosurePerc.Value = ""

'Empty secondary enclosure material combo box
    cbSecEnclosureMat.Clear

'Fill secondary enclosure material combo box
    cbSecEnclosureMat.List = Application.WorksheetFunction.Transpose(Range("rngEnclosure"))

'GLAZING
'Empty W2W text box
    txbW2W.Value = ""

'Empty primary glazing % text box
    txbPrimeGlazingPerc.Value = ""

'Empty primary glazing material combo box
    cbPrimeEnclosureMat.Clear

'Fill primary glazing material combo box
    cbPrimeGlazingMat.List = Application.WorksheetFunction.Transpose(Range("rngGlazing"))

'Empty secondary glazing % text box
    txbSecGlazingPerc.Value = ""

'Empty secondary glazing material combo box
    cbSecGlazingMat.Clear

'Fill secondary glazing material combo box
    cbSecGlazingMat.List = Application.WorksheetFunction.Transpose(Range("rngGlazing"))

'Set focus on project name text box
    txbProjectName.SetFocus
End Sub
Solution

  • It was a variable error. Mistyped a variable further down in the code that was causing the ComboBox to clear after being populated with the list.

    'Empty primary glazing material combo box
cbPrimeEnclosureMat.Clear

    Should be:

    'Empty primary glazing material combo box
    cbPrimeGlazingMat.Clear