Prevent duplicates in combo box but not in the originally transferred sheet.

Hello Everyone on this Forum.

ok. Here it goes.( I am new to VBA and please have patience as this seems a layman's understanding)
i have a user form with ComboBox1 for "Company"entries and various text boxes for addresses viz "tel no", "fax no", "Po box", "mail I'd" etc.
For the ComboBox1 have assigned the the row source( in the properties table) to a dynamic named range called "Company".
This dynamic range named "Company" was created in excel.
I have a command button called "Submit"
Under the command button click ,the information in the comboBox1 and various text boxes do get transferred to a sheet finding the last empty row.
So far so good and now comes the tricky part.
when I again enter as a new entry for previous entered company in the comboBox1, I find there are already duplicates in the drop down list.
first of all I want the duplicates not to be seen,only in the drop down list ( but remain in the transferred sheet)
Secondly since the previous entered company has all the rest of information such as "tel no", "fax no", "Po box", "mail I'd" etc. I do not want to enter the same information again but get the respective text boxes populated so that when I finish with the whole user form and press the "Submit" button, the information get transferred to the sheet.
please note that every new use of user form has some new details every time. Such as date, inquiry number, project details , region etc.

WOW. Thanks for the quick response. This forum is great with lot of visitors as well in a short time.


I added your previous code of Userform_Activate ()
'but got the debug yellow color at Me.ComboBox1.clear
'I also changed the Userform name to my given name usfrprojectdetails_Activate() but still I 'am getting the ComboBox1 with duplicate entries and the other textbox values are not being 'retreived from the existing sheet. Please also note that not every entry is a recurring entry. There are a lot of new "Company" entries as well.


Private Sub cmndSubmit_Click()
Dim erow As Double
Sheets("sheet1").Range("C3").Value = TextBoxProject.Value
Sheets("sheet1").Range("C4").Value = ComboBox1.Value
Sheets("sheet1").Range("H3").Value = TextBoxRegion.Value
Sheets("sheet1").Range("C2").Value = TxtBoxInquiry.Value
Sheets("sheet1").Range("C5").Value = TextBoxContact.Value
Sheets("sheet1").Range("C6").Value = TextBoxMobile.Value
Sheets("sheet1").Range("H2").Value = TextBoxDate.Value
Sheets("sheet1").Range("H4").Value = TextBoxTel.Value
Sheets("sheet1").Range("H5").Value = TextBoxMail.Value

erow = Sheet8.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Sheets("sheet8").Cells(erow, 1).Value = TextBoxDate.Value
Sheets("sheet8").Cells(erow, 2).Value = ComboBox1.Value
Sheets("sheet8").Cells(erow, 3).Value = TextBoxPOBox.Value
Sheets("sheet8").Cells(erow, 4).Value = TextBoxPlace.Value
Sheets("sheet8").Cells(erow, 5).Value = TextBoxTel.Value
Sheets("sheet8").Cells(erow, 6).Value = TextBoxFax.Value
Sheets("sheet8").Cells(erow, 7).Value = TextBoxMail.Value
Sheets("sheet8").Cells(erow, 8).Value = TextBoxWeb.Value
Sheets("sheet8").Cells(erow, 9).Value = TextBoxContact.Value
Sheets("sheet8").Cells(erow, 10).Value = TextBoxMobile.Value
Sheets("sheet8").Cells(erow, 11).Value = TextBoxProject.Value
Sheets("sheet8").Cells(erow, 12).Value = TextBoxRegion.Value
Sheets("sheet8").Cells(erow, 13).Value = TxtBoxInquiry.Value


(My reply): 'I just did two entries to test.
'Your existing code here

'Then after all of the existing code:
Me.TextBoxDate.Text = vbNullString
Me.TextBoxPlace.Text = vbNullString


Worksheets("sheet1").Select
Worksheets("sheet1").Range("A1").Select
Unload Me



End Sub

' I added your previous code of Userform_Activate ()
'but got the debug yellow color at Me.ComboBox1.clear
'I also changed the Userform name to my given name usfrprojectdetails_Activate() but still I 'am getting theComboBox1 with duplicate entries and the other textbox values are not being 'retreived from the existing sheet.


Private Sub usfrprojectdetails_Activate()
Dim ws As Worksheet
Dim cllCompanies As Collection
Dim arrCompanies() As String
Dim varCompany As Variant
Dim i As Long

Set ws = Sheets("Sheet8")
Set cllCompanies = New Collection

On Error Resume Next
For Each varCompany In ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)).Value
cllCompanies.Add varCompany, varCompany
Next varCompany
On Error GoTo 0

Me.ComboBox1.clear
If cllCompanies.Count > 0 Then
ReDim arrCompanies(1 To cllCompanies.Count)
For i = 1 To cllCompanies.Count
arrCompanies(i) = cllCompanies.Item(i)
Next i
Me.ComboBox1.List = arrCompanies
End If

Set ws = Nothing
Set cllCompanies = Nothing
Erase arrCompanies
End Sub

Please Login or Register  to view this content.

PHP Code: 

TERHAB 3352727.
Thank you once again for your reply. I tried to find any faults but could not . So I am uploading the workbook for your reference 


Hello!! Dear Visitors,
This is for your benefit.
For the above query, I received an answer linked to the following
http://www.mrexcel.com/forum/excel-q...combo-box.html