CODEWITHSUNDEEP
excelvbacomboboxactivexactivexobject
Jared127
Jared127

Reputation: 37

ActiveX Combo Box Selection Disappears After Selection

My VBA code works successfully when loading the combo box with different list based on a Selected Case. However, when I select an option from the drop down, the selection does not load into the Combo Box. The Combo Box goes blank.

Here is my VBA code:

Private Sub cbxTrainingSubject_DropButtonClick()

    cbxTrainingSubject.Clear

    Set shTables = ThisWorkbook.Sheets("Tables")
    Set shForm = ThisWorkbook.Sheets("Form")
    Dim rng As Range

    Select Case cbxTrainingArea.Value
        Case "Assembly"
            Set rng = shTables.ListObjects("assemblyTraining").ListColumns("Assembly Training").DataBodyRange
            shForm.cbxTrainingSubject.List = Application.Transpose(rng.Value)
        Case "Fabrication"
            Set rng = shTables.ListObjects("FabTraining").ListColumns("Fab Training").DataBodyRange
            shForm.cbxTrainingSubject.List = Application.Transpose(rng.Value)
    End Select

End Sub

Here is the Properties window for the target Combo Box: Combo Box Properties

When I make a selection, the Combo Box stays blank.

Upvotes: 1

Views: 89

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Probably easier to use cbxTrainingArea_Change

For example:

Private Sub cbxTrainingArea_Change()
    
    Dim wsTables As Worksheet, rng As Range
    
    Set wsTables = ThisWorkbook.Sheets("Tables")
    Select Case cbxTrainingArea.Value
        Case "Assembly": Set rng = wsTables.Range("Assembly")
        Case "Fabrication": Set rng = wsTables.Range("Fabrication")
    End Select
    
    If Not rng Is Nothing Then
        cbxTrainingSubject.List = Application.Transpose(rng.Value)
    Else
        cbxTrainingSubject.Clear
    End If
    
End Sub

Upvotes: 1

Related Questions