problem repeat rowsorce combobox every time click another cell

[abdelfattah]

hello
i have a problem i insert activeX combobox into sheet and i add this code every thing is ok in the first time but when i move from combobox to any cell i found the list in combobox is repeat continuously when i move to any cell

Private Sub ComboBox1_DropButtonClick()
With ComboBox1
.AddItem "jan"
.AddItem "feb"
.AddItem "mar"
.AddItem "apr"
.AddItem "may"
.AddItem "jun"
.AddItem "jul"
.AddItem "aug"
.AddItem "sep"
.AddItem "oct"
.AddItem "nov"
.AddItem "dec"
End With

End Sub

i hope any body help
thanks

 

[DanteAmor]

I don't understand why you load the combo every time you select it, but with the following you can solve it:

Private Sub ComboBox1_DropButtonClick()
  With ComboBox1
    .Clear
    .AddItem "jan"
    .AddItem "feb"
    .AddItem "mar"
    .AddItem "apr"
    .AddItem "may"
    .AddItem "jun"
    .AddItem "jul"
    .AddItem "aug"
    .AddItem "sep"
    .AddItem "oct"
    .AddItem "nov"
    .AddItem "dec"
  End With
End Sub

 

[abdelfattah]

hi, Dante actually i don't understand this statement

I don't understand why you load the combo every time you select it

my code supposes load the combobox one time and works normally without any repeat every time i select it
but when add ".clear" it become the combobox is empty when i choose item not show the selected item

 

[DanteAmor]

Try this

Private Sub ComboBox1_DropButtonClick()
  Dim dato
  With ComboBox1
    dato = .Value
    .Clear
    .AddItem "jan"
    .AddItem "feb"
    .AddItem "mar"
    .AddItem "apr"
    .AddItem "may"
    .AddItem "jun"
    .AddItem "jul"
    .AddItem "aug"
    .AddItem "sep"
    .AddItem "oct"
    .AddItem "nov"
    .AddItem "dec"
    .Value = dato
  End With
End Sub

 

[abdelfattah]

yes, that works , could you explain me why my code doesn't work correctly in the normal situation and when you define the variable what's the type and what means this at begening code and end code?

dato = .Value

 

[DanteAmor]

I declare the variable, you should always declare variables. It should actually be
Dim data as string
I was missing that part.

At the beginning dato = .value, it passes the value of the combo to the variable "dato" and at the end the content of the "dato" is returned to the combo.

 

[abdelfattah]

thanks Dante for this explanation and your code works greatly

 

[DanteAmor]

Glad we could help & thanks for the feedback