AddItem Method

[Todd Huttenstine]

Private Sub ComboBox1_DropButtonClick()
ComboBox1.AddItem "Jan Week 1"
ComboBox1.AddItem "Jan Week 2"
ComboBox1.AddItem "Jan Week 3"
ComboBox1.AddItem "Jan Week 4"
ComboBox1.AddItem "Jan Week 5"
ComboBox1.AddItem "Jan MTD"
End Sub

hey guys
The above code adds the specified values to ComboBox1 when
a user clicks the drop down box on the combobox. Problem
is when the user clicks the dropdown button again, the
values are added again so now there are 2 sets of the same
values. If the user clicks it again, a 3rd set is listed
and so on... How do I prevent these values from adding up?


Thank you
TOdd Huttenstine

 

[Dick Kusleika]

Todd

Make the first line

Me.ComboBox1.Clear

If you're adding the same thing every time, why are using this event? Why
not do it in the UserForm_Initialize event?

 

[Patrick Molloy]

It's very unusual to load a combo this way. one would
normally load it before the user selects an item.
Anyway, set a boolean to True

Public bLoaded as Boolean
in the general part of a module or userform - you don't
say where you use the combo.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Private Sub ComboBox1_DropButtonClick() If NOT bLoaded then
ComboBox1.AddItem "Jan Week 1"
ComboBox1.AddItem "Jan Week 2"
ComboBox1.AddItem "Jan Week 3"
ComboBox1.AddItem "Jan Week 4"
ComboBox1.AddItem "Jan Week 5"
ComboBox1.AddItem "Jan MTD"

bLoaded = True
End If

 

[Todd Huttenstine]

Hey thanks. I used the .clear method as suggested. The
combobox is located on a worksheet and is located to look
and act as part of a Pivot Table I am using.


After the combobox1.clear I used the additem method to add
items to the box.

Thanks
Todd