Excel VBA Drop Down boxes

[Guest]

Hello All:
I am a little confused here and can't seem to figure out how this issue can
be resolved. I have created several combo boxes giving customers a list to
choose from, however, when a selection is made the combo box will give the
list again. In other words, the orginal list will show up in multiples when a
selection is made. I have looked at the properties, but cannot find what sets
this action. Can someone help?

 

[Guest]

Sounds like you are populating the combobox on the dropdown, if you do that
you must clear it first. If thats not it, paste the code where you are
loading it

 

[Guest]

John,
Thanks for the reply

I am populating it, but through code:

Private Sub ComboBox2_DropButtonClick()
ComboBox2.AddItem ""
ComboBox2.AddItem "NEW_LATE_ENROLLMENT"
ComboBox2.AddItem "LATE_ENROLLMENT"
ComboBox2.AddItem "NEW_ENROLLMENT"
ComboBox2.AddItem "DECREASE"
ComboBox2.AddItem "INCREASE"
ComboBox2.AddItem "PRE_ENROLLMENT"
Range("F11") = ComboBox2.Value
End Sub

The drop down gives a list like:
Blank
NEW_LATE_ENROLLMENT
LATE_ENROLLMENT
NEW_ENROLLMENT
DECREASE
INCREASE
PRE_ENROLLMENT

When someone makes a selection the list looks like this:
Blank
NEW_LATE_ENROLLMENT
LATE_ENROLLMENT
NEW_ENROLLMENT
DECREASE
INCREASE
PRE_ENROLLMENT
Blank
NEW_LATE_ENROLLMENT
LATE_ENROLLMENT
NEW_ENROLLMENT
DECREASE
INCREASE
PRE_ENROLLMENT

The more selections that are made, the more it seems to replicate. It's not
connected to any cells except for the selection so that the customer can see
what they have chosen. I looked for a property to keep it from replicating,
but cannot find. Do you know?

I am not sure I understand "paste the code where you are loading it"?

 

[Guest]

Place your code under:

Private Sub UserForm_Initialize()
End Sub

Except for this line:
Range("F11") = ComboBox2.Value

leave it where it is, like this:
Private Sub ComboBox2_DropButtonClick()
Range("F11") = ComboBox2.Value
End Sub

 

[Guest]

Ralph,
This works Great Thanks! However, I have other combo boxes that are doing
the same on the same form. When I try to create another userform, I get the
amiguous error. Any ideas?

 

[Guest]

Not sure what you mean by ambiguous. The problem arises because every time
the user clicks on the combo box the code runs to add items to it. You should
probably create a sub to load the items for each combo box then call it from
the Private Sub UserForm_Initialize

Maybe someting like:

Private Sub popComboBox2
ComboBox2.AddItem ""
ComboBox2.AddItem "NEW_LATE_ENROLLMENT"
ComboBox2.AddItem "LATE_ENROLLMENT"
ComboBox2.AddItem "NEW_ENROLLMENT"
ComboBox2.AddItem "DECREASE"
ComboBox2.AddItem "INCREASE"
ComboBox2.AddItem "PRE_ENROLLMENT"
End Sub

Then you could call it in the initialize:

Private Sub UserForm_Initialize
Call popComboBox2
End Sub

UserForm_Initialize fires once when the form opens, so the combo boxes will
only be populated once.

Repeat the process for each combo box, hope this makes sense.