Excel Userform List keeps repeating itself

[Candyman7]

Hi guys, I have the below vba code for a userform. Every time I select the list, it keeps repeating itself and keeps getting longer. Please review the below code and provide suitable solutions. Thank you.


Private Sub cboClass_DropButtonClick()
'Populate control.
Me.cboClass.AddItem "Mammals"
Me.cboClass.AddItem "Reptiles"
Me.cboClass.AddItem "Birds"
Me.cboClass.AddItem "Amphibians"
Me.cboClass.AddItem "Fish"
End Sub
Private Sub cboConservationStatus_DropButtonClick()
'Populate control.

Me.cboConservationStatus.AddItem "Endangered"
Me.cboConservationStatus.AddItem "Extinct"
Me.cboConservationStatus.AddItem "Nearly Extinct"
Me.cboConservationStatus.AddItem "Stable"
Me.cboConservationStatus.AddItem "Threatened"
Me.cboConservationStatus.AddItem "Critically Endangered"
Me.cboConservationStatus.AddItem "Overpopulated"
End Sub
Private Sub cboSex_DropButtonClick()
'Populate control.
Me.cboSex.AddItem "Male"
Me.cboSex.AddItem "Females"
End Sub

Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.cboClass.Value
.Cells(lRow, 2).Value = Me.cboSex.Value
.Cells(lRow, 3).Value = Me.cboConservationStatus.Value
.Cells(lRow, 4).Value = Me.txtComment.Value
End With
'Clear input controls.
Me.cboClass.Value = ""
Me.cboSex.Value = ""
Me.cboConservationStatus.Value = ""
Me.txtComment.Value = ""
End Sub
Private Sub cmdClose_Click()
'Close UserForm.
Unload Me
End Sub
Private Sub Class_Click()

Me.cboConservationStatus.Clear
Me.cboSex.Clear
End Sub

 

[My Aswer Is This]

I would do it this way.
The Cbo box is loaded when userform is opened.
Put this script in your userform. Not in the control.

Private Sub UserForm_Initialize()
'Modified  1/31/2020  3:47:53 AM  EST
With Me.cboClass
    .Clear
    .AddItem "Mammals"
    .AddItem "Reptiles"
    .AddItem "Birds"
    .AddItem "Amphibians"
    .AddItem "Fish"
End With
End Sub

 

[Candyman7]

I would do it this way.
The Cbo box is loaded when userform is opened.
Put this script in your userform. Not in the control.

Private Sub UserForm_Initialize()
'Modified  1/31/2020  3:47:53 AM  EST
With Me.cboClass
    .Clear
    .AddItem "Mammals"
    .AddItem "Reptiles"
    .AddItem "Birds"
    .AddItem "Amphibians"
    .AddItem "Fish"
End With
End Sub

Thank you for your reply, I tried modifying the code as you suggested. However, I am now not able to select any option at all. The list displays the classes, but am not able to select it. Not sure what the problem is now.

 

[My Aswer Is This]

Remove any code you have in the cbo class

I assume this is a combobox.

So you have the values added into the combobox.
Automatically when you open Userform.
What do you want to happen when you choose "Birds"

 

[Candyman7]

Remove any code you have in the cbo class

I assume this is a combobox.

So you have the values added into the combobox.
Automatically when you open Userform.
What do you want to happen when you choose "Birds"

Yes, Just realised there was some redundant code before the cbo class, works like a charm now. Thank you for your help.

 

[My Aswer Is This]

And you said:

I tried modifying the code as you suggested
I wanted you to completely remove the code you had in the control and put my code in the Userform
now it runs automatically when userform is opened
Double click on userform and post in my code​

 

[My Aswer Is This]

Yes, Just realised there was some redundant code before the cbo class, works like a charm now. Thank you for your help.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Candyman7]

And you said:
I tried modifying the code as you suggested
I wanted you to completely remove the code you had in the control and put my code in the Userform
now it runs automatically when userform is opened
Double click on userform and post in my code

Yes, your code was only for cboClass, I wanted to update it for cboConservationStatus and cboSex as well.

 

[My Aswer Is This]

I figured you would see how to do it and could add the others like this for example:

Private Sub UserForm_Initialize()
With Me.cboClass
    .Clear
    .AddItem "Mammals"
    .AddItem "Reptiles"
    .AddItem "Birds"
    .AddItem "Amphibians"
    .AddItem "Fish"
End With
With Me.cboConservationStatus
    .Clear
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Thee"
    .AddItem "Four"
    .AddItem "Five"
End With
End Sub

And on and on as many as you need.

 

[Candyman7]

I figured you would see how to do it and could add the others like this for example:

Private Sub UserForm_Initialize()
With Me.cboClass
    .Clear
    .AddItem "Mammals"
    .AddItem "Reptiles"
    .AddItem "Birds"
    .AddItem "Amphibians"
    .AddItem "Fish"
End With
With Me.cboConservationStatus
    .Clear
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Thee"
    .AddItem "Four"
    .AddItem "Five"
End With
End Sub

Yes, I've successfully updated the entire code. Thank you kind sir.