Combo box items clear problem in excel

I am using the following code to populate a combo box placed on Sheet1 of an excel file to add items from Access database table named Company.

Code:

---

Private Sub cboCompany_DropButtonClick()
    Dim con As Object
    Dim rs As New ADODB.Recordset
    Set con = CreateObject("ADODB.Connection")
    con.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ActiveWorkbook.Path & "\ServiceTaxData.mdb"
    With rs
            .ActiveConnection = con
            .CursorLocation = adUseClient
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Source = "select * from company"
            .Open
    End With
    cboCompany.Clear
    If rs.RecordCount < 0 Then
    MsgBox "No company name found"
    Else
    For i = 1 To rs.RecordCount
    cboCompany.AddItem rs.Fields(1)
    rs.MoveNext
    Next i
    End If
End Sub

---

The problem is that it adds the items in the combo box every time the arrow button is clicked ie if there are forur items in the table, on first click it adds 4 items, on 2nd click it again adds four items results in 8 items.
So I have added the line cbocompany.clear
Now the problem is that it clears the combo box items as soon as an item is selected from the list.
With debugging break mode, what I have seen that after adding the last item ie after Next I for last item it again moves to cbocompany.clear line.
How can I fix this?

Why are you adding the code to add data in

Code:

---

Private Sub cboCompany_DropButtonClick

---

you should have it in form load event
Edit: I believe its

Quote:

---

Private Sub UserForm_Activate()

---

in VBA :D

I am using the same in excel vba.

You didn't answer VBFnewcomer's question.

Of which question? This one?

Quote:

---

I believe its in VBA

---

Regarding this one:
Why are you adding the code to add data in

Quote:

---

Code:
Private Sub cboCompany_DropButtonClick

---

Otherwise where should I add the code?

Also I am not adding the data to a combo box on a User Form. I alreday mentioned in the first query that that the combo box is on sheet1 which I have added through Control Forms.

Quote:

---

Otherwise where should I add the code?

---

Code:

---

Private Sub UserForm_Activate()

---

Quote:

---

combo box is on sheet1 which I have added through Control Forms.

---

where is this control forms

Sorry, the same should be read as Control Toolbox under View\Toolbars\Control Toolbox.

Ok now I have found your combobox. Simply right click the sheet tab->view code. Now click object combo you might find in the code window select worksheet. Now click property combo you might find next to it and select Activate. Or you may simply copy

Quote:

---

Private Sub UserForm_Activate()

End sub

---

and paste it in the code window. Now add the code to populate the combo here.