Combox list with Collection, list getting updated multiple time

[upguy]

Thanks

bakerman2 & AMYasserKhalil, AMjindon. Helping me. I was stunk in this from last two days but in sort time you people resolved my problem. I appreciate your help and work.

Thanks and regards
upguy


Hello Experts,
I have created combo box with using of collection, I have list a (data) in sheet1 range("A1") to Last row (variable). I have added combo-box item with collection.


I am facing issue the number of time I click on Drop down of combo-box the list is updating that number of time.

Please help me to resolve this. I am new to VBA.

Thanks an regards.

upguy

[YasserKhalil]

Hello
Put your code in another event

Please Login or Register  to view this content.

[upguy]

Sorry in last mail I have not give the code, Please find the code.

Below is a code:-

Private Sub ComboBox1_DropButtonClick()
Dim ColList As Collection
Dim Lr As Long, I As Long, J As Long

Lr = Sheet1.Range("A1").Cells(Rows.Count, 1).End(xlUp).Row

Set ColList = New Collection

With Sheet1.Range("A1").Resize(Lr)
For I = 2 To Lr
ColList.Add .Cells(I, 1).Value, CStr(.Cells(I, 1))
Next

For J = 1 To ColList.Count
Me.ComboBox1.AddItem ColList(J)
Next

End With
End Sub

[YasserKhalil]

I didn't understand what you're after. I think you have to put the code in userform_Intialize event not in ComboBox1_DropBuutonClick

[jindon]

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[upguy]

Private Sub UserForm_Initialize()
Dim ColList As Collection
Dim Lr As Long, I As Long, J As Long

Lr = Sheet1.Range("A1").Cells(Rows.Count, 1).End(xlUp).Row

Set ColList = New Collection

With Sheet1.Range("A1").Resize(Lr)
For I = 2 To Lr
ColList.Add .Cells(I, 1).Value, CStr(.Cells(I, 1))
Next

For J = 1 To ColList.Count
Me.ComboBox1.AddItem ColList(J)
Next
End With

Lr1 = Sheet1.Range("b1").Cells(Rows.Count, 1).End(xlUp).Row

With Sheet1.Range("b1").Resize(Lr1)
For I = 2 To Lr1
ColList.Add .Cells(I, 1).Value, CStr(.Cells(I, 1))
Next

For J = 1 To ColList.Count
Me.ComboBox2.AddItem ColList(J)
Next
End With

End Sub

[bakerman2]

Please amend your posts to use Code Tags when posting code.

Please Login or Register  to view this content.

[upguy]

Thanks

bakerman2 & AMYasserKhalil, AMjindon. Helping me. I was stunk in this from last two days but in sort time you people resolved my problem. I appreciate your help and work.

Thanks and regards
upguy