ActiveX Combo Box Selection Disappears After Selection

Question

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.

• Tim Williams: Have you tried stepping through the code to see what's going on?
• Tim Williams: Why not update cbxTrainingSubject when cbxTrainingArea is changed? That might be more reliable.
• Jared127: @TimWilliams cbxTrainingSubject is dependent on cbxTrainingArea value selection . Can you share some suggested code to help me better understand?
• Infrequent Coder: If i am understanding the dilemma properly, I would remove the clear method at the beginning of the procedure. It isn't necessary when alternating lists anyways and might be clearing the value immediately after clicking as well. I would also check other events tied to that combo box to ensure they are not interfering.

Answer 1

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