CODEWITHSUNDEEP
excelvbacomboboxuserformnamed-ranges
406LQE
406LQE

Reputation: 53

Populating ComboBox questions - duplicated values each time the box is selected, and named range does not populate

I am new to VBA and StackOverflow, so I apologize in advance for any mistakes.

I have a UserForm that, ultimately, will have 16 ComboBoxes. All 16 of these ComboBoxes will have the same 5 survey response choices).

My first attempt was to populate them like this, repeated 16 times for each ComboBox:

Private Sub cboAE1A_DropButtonClick()
   'Populate control.
   Me.cboAE1A.AddItem "Strongly disagree"
   Me.cboAE1A.AddItem "Disagree"
   Me.cboAE1A.AddItem "Neither agree nor disagree"
   Me.cboAE1A.AddItem "Agree"
   Me.cboAE1A.AddItem "Strongly agree"
End Sub

When I test the form, however, selecting the ComboBox more than once duplicates the response options. So, if I click it once everything is fine. On the second time, I see 3 sets of response options, so on and so forth. Why is that? What can I do to prevent it?

Second, in an attempt to avoid repeating the above code 16 times, I found this link and tried to adapt it for my purposes:

Private Sub ufrmGenderTraining_Initialize()
   'Populate AE1A combo box.
   Dim rngRespuestas As Range
   Dim ws As Worksheet
   Set ws = Sheets("INPUTS")
   For Each rngRespuestas In ws.Range("Respuestas")
   Me.cboAE1A.AddItem rngRespuestas.Value
   Next rngRespuestas
End Sub

But the ComboBox is blank. Any ideas how to fix it, and how to efficiently populate these 16 ComboBoxes with the same list? If you can also explain like I'm 5 years old, that would be much appreciated!

Thanks in advance.

Upvotes: 1

Views: 5548

Answers (2)

luke_t
luke_t

Reputation: 2985

You were originally using the DropButtonClick event.

This means each time you click on the ComboBox, it will add the same items to the ComboBox list.

Hence, the first time you click on the drop-down it shows once, second time twice etc.

You could also write less code by looping through each Combobox and applying the same options to each of them. Example:

Dim comboItems() As Variant
Dim ct As Control
Dim i As Long

comboItems() = Array("Strongly disagree", _
                     "Disagree", _
                     "Neither agree nor disagree", _
                     "Agree", _
                     "Strongly agree")

For Each ct In Me.Controls

    If TypeName(ct) = "ComboBox" Then

        For i = LBound(comboItems) To UBound(comboItems)
            ct.AddItem comboItems(i)
        Next i

    End If

Next ct

Ensure the above code is used in the UserForm_Initialize event within the UserForm.

Updated If statement:

If TypeName(ct) = "ComboBox" And _
   ct.Name <> "cboGender" And _
   ct.Name <> "cboDepartment" Then

Upvotes: 0

Nick Peranzi
Nick Peranzi

Reputation: 1375

In your first example, the event fires every time the drop down button is clicked for a given ComboBox. Therefore, when the form loads, there are no available options. When the user first clicks the drop down for the first ComboBox, the event fires and your code adds the 5 options. The user selects an option and continues.

Later, the user realized she wants to change her response to the first question. She clicks the drop down button again for the first ComboBox; your code then executes again, adding 5 more options, each of which are duplicates.

I see you have fixed the Initialize event code to do what you intended; I believe that is a better option than your first one. If, however, you would prefer to modify your original code, add a line that checks the ListCount property of the ComboBox first. That will prevent the options from being added if the ComboBox has already been populated with responses. 

Private Sub cboAE1A_DropButtonClick() 
'Check for existence of items
If Me.cbo.AE1A.ListCount = 0 Then
    'Populate control. 
    Me.cboAE1A.AddItem "Strongly disagree" 
    Me.cboAE1A.AddItem "Disagree" 
    Me.cboAE1A.AddItem "Neither agree nor disagree" 
    Me.cboAE1A.AddItem "Agree" 
    Me.cboAE1A.AddItem "Strongly agree" 
End If
End Sub

Upvotes: 2

Related Questions