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?
Re: Combo box items clear problem in excel
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
Re: Combo box items clear problem in excel
I am using the same in excel vba.
Re: Combo box items clear problem in excel
You didn't answer VBFnewcomer's question.
Re: Combo box items clear problem in excel
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.
Re: Combo box items clear problem in excel
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
Re: Combo box items clear problem in excel
Sorry, the same should be read as Control Toolbox under View\Toolbars\Control Toolbox.
Re: Combo box items clear problem in excel
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.