none
How do I validate for duplicate entries in a combo box control using VBA? RRS feed

  • Question

  • I have a userform in Excel 2016 that requires entering new categories into a combo box control. I've already created the code to populate the combobox with a list of existing categories from a defined name range. The code also includes adding the new item to the defined name list on a separate sheet.

    What I would like to do is when the user clicks on the 'add category' button I need validation that the item the user types in doesn't already exist in the list. I don't want to add duplicate items

    How can I accomplish this with VBA code? I'm unable to find sample code on the net to do this. I'm trying this code:

    'Check for Duplicate Categories
        For Each item As String In cboCatgList.Items
          If Item.ToLower.Contains(cboCatgList.Text.ToLower) Then
               MsgBox("Duplicate value")
                cboCatgList.Text = ""
          End If
     Next

    I'm getting an error on the for statement and I don't know why. and since I'm getting an error there I don't know if the rest of the code will work


    Keith Aul

    Friday, July 15, 2016 7:54 PM

Answers

All replies

  • Hi KeithAul,

    please visit the links below will give you example of removing duplicates from the combobox.

    VBA Remove Duplicates in ComboBox

    excel vba eliminate combo list duplicate.

    Only unique records in a Combobox (VBA)

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, July 16, 2016 9:43 AM
    Moderator
  • The attached image shows the code I'm using for the initialize routine for a user form. When I click on my shape to open the user form the only code there is :

    frmCatgList.Show

    After the above code is executed then it goes to the initialize routine and that code is on the image attached to this reply. As you can see I have an arrow pointing to the line of code where I'm getting the error on.

    For some reason, I'm getting this error when previously I wasn't and I didn't change anything in the code.  So does anyone know why I'm get this error and how can I fix it?


    Keith Aul

    Wednesday, July 27, 2016 8:57 PM
  • Hi KeithAul,

    did you check the Range "Category list" in worksheet "Lists"?

    please check it and let us know it is properly defined.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 28, 2016 8:43 AM
    Moderator
  • I had checked the range 'CategoryList' in the name manager right before i posted this error. However after reading your solution I did notice the following:

    =OFFSET(Lists!#REF!,0,0,COUNTA(Lists!$A:$A)-1,1)

    I saw '#REF' in the formula. 

    So I began to wonder why this happen.

    So I began testing the userform and going to the list. When I delete rows manually on the sheet, it didn't change this formula until I deleted the first row in the range which is $A$2. When the formula is changed back and corrected to the following, everything works fine:

    =OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)

    So my question is why does the formula change when I delete the first row in that defined range which is A2, but when I delete other rows in the range, for formula remains unchanged?

    How would I be able to account for this in VBA code? If a row gets deleted how would I be able to redefine it in VBA code?


    Keith Aul

    Thursday, July 28, 2016 11:21 AM
  • Hi KeithAul,

    when you delete the range $A$2. at that time Formula loss the reference of $A$2.

    because you delete it.

    but you can notice that .

    after you delete $A$2 the cell below to $A$2 will move to up and take place at $A$2.

    so when you correct the formula and run it at that time $A$2 is available so error will not occur.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 29, 2016 2:44 AM
    Moderator