Check ListBox Items before Adding......Duplicates not wanted

[Rodopi]

Using Excel 2000 & VBA, I have upon a userform a combobox populated with over 200 items from which a user may select or add their own.

When user makes selection they click a command_button to copy the selection to a listbox.

Prior to the copy I want to check to see that their selection is not already within the listbox as duplicates must not be present.

I have tried various methods but all end in failure...I am new to VBA so any help at all would be much appreciated.

My questions therefore are as follows:

1. How can I make the comparison?

2. Can it be done without too much impact upon
performance, as 100+ items may regularly be a
requirement.


Regards
Rodopi

 

[Zathras]

Take a look at the Collection object. There is a property to allow/disallow duplicates. If I were doing this, I would maintain a Collection of the items in the list box and before adding to the list, try to add to the collection (trapping the error). If the .Add to the collection worked ok, then add to the list.

 

[Rodopi]

Thanks Zathras,
Sounds like an elegant solution...good trade off with memory against processing power.

I will give it a go and let you know.


Many thanks again
Rodopi

 

[Rodopi]

Hi Zathras,

Ok.....I have found a solution based upon your idea, but doesn't map it entirely.

Could only find the following properties that map to a collection: Count, Item & Remove.

Did some investigation and found the following (Free) Active X Object 'FlexBag' from spidereye.com.

This had a Duplicate Property and looked perfect until I noticed that it was for VB/VBA version 5...I use version 6 and I couldn't get comfirmation that this was portable to v.6.

My final workable solution was to use a collection and check the selected item against all objects within collection using For/Next loop and setting Boolean to true if duplicate found.

Thanks Zathras for pointing me in the right direction.

Regards
Rodopi

 

[Zathras]

Sorry, it's not a property. It is all in how you use the "Add" method. (That's what happens when one has to use different languages at different times.) Look it up in the help file.

The general syntax is:

   object.Add item, key, before, after

You can have duplicate items, but you can't have duplicate keys. So for example, if you try to do the following:

Sub test()
Dim col As New Collection
  col.Add 2, "3"
  col.Add 4, "4"
  col.Add 1, "1"
  col.Add 2, "5"
  col.Add 6, "1"
End Sub

There is no problem adding the duplicate item value 2 because the key "5" is still unique. But it fails when attempting to add item value 6 even though the item is unique because the key ("1&quot is a duplicate.

So the trick is to just take whatever you are adding for the item and use it for the key, too. (Then trap for the error.) Something like this (Create a user form and drop a listbox and a command button in it. Then all this code goes into the UserForm1 code page):
[blue]

Option Explicit
Dim col As Collection

Private Sub UserForm_Initialize()
  Set col = New Collection
End Sub

Private Sub CommandButton1_Click()
  AddToList "Apples"
  AddToList "Oranges"
  AddToList "Grapes"
  AddToList "Oranges"
End Sub

Private Sub AddToList(SelectedItem As String)
  On Error GoTo noadd
  col.Add SelectedItem, SelectedItem
  UserForm1.ListBox1.AddItem (SelectedItem)
  Exit Sub
noadd:
  MsgBox SelectedItem & " is already selected."
End Sub

[/color]
Note that the MsgBox is shown when the error occurs.

 

[Rodopi]

Hi,

Sorry I misunderstood your previous advice.....you have made it perfectly clear now with your very simple and easy working examples....thank you very much.

I will implement this method in my project.

Thank you once again for your help and assistance.

Regards
Rodopi