-1

I am in the process of learning VBA and I need help developing a code that will check an entry against stored values (in an excel column), but if the new entry is not already stored it will be stored with the list of existing values (in another excel column). If I where to input a second entry, the code would see if the second entry is not already stored, if not then it would add to the list of stored values.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
mflo
  • 1
  • 1
  • this would be to update a dropdown list in a user form – mflo Mar 31 '18 at 00:32
  • Have you tried anything yet? If not, please do. Once you have a *specific* problem, please [edit](https://stackoverflow.com/posts/49582994/edit) the question, post your code, and point to the problem. See [What topics can I ask about here?](https://stackoverflow.com/help/on-topic). – 41686d6564 stands w. Palestine Mar 31 '18 at 01:32

1 Answers1

0

Using a VBA Collection is a handy way to create a list with no duplicates.

This test code shows how it works. It's self explanatory, but basically you add items to the list with StoreItem, list them on the active worksheet with ListItems and reset the collection with ClearItems. Run test as an example.

By default, collections don't allow duplicates and would give an error. We simply ignore the error for the .Add command, and then we get a unique list.

Instead of dumping the unique fields to the worksheet in ListItems, you just need to add them to your combo box instead with .AddItem or into your input range, depending on which type of combo box and how you're using it. 

Option Explicit

Public cItems As New Collection

Sub StoreItem(iValue As Variant)
    'store value in collection. (ignore error if duplicate)
    On Error Resume Next
    cItems.Add iValue, iValue
    On Error GoTo 0
End Sub

Sub ListItems()
    'dump collection onto active worksheet
    Dim x As Long
    For x = 1 To cItems.Count
        ActiveSheet.Cells(x, 1) = cItems(x)
    Next x
End Sub

Sub ClearItems()
    'empty the collection
    Set cItems = Nothing
End Sub

Sub test()
    StoreItem "dog"
    StoreItem "cat"
    StoreItem "bird"
    StoreItem "cat"
    StoreItem "dog"
    StoreItem "snake"
    ListItems
End Sub

More Information:

...and two of my answers about working with controls:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105