Maintaining a list of unique entries in a list

[gordon.moar]

I have a form with multiple comboboxes where names can be selected,
all driven from a common array filled from a range on a hidden data
sheet. What I'm trying to achieve, and probably just missing
something really simple, is management of what happens when a name
needs removed or re-added to the combobox list.

I know I can handle the removal of one of the items in the list in
various ways, e.g. by removing it from the array that drives all the
comboboxes.

The problem I'm having is coming up with an elegant way for an item to
reappear on the list of 'free' names if a once-populated combobox is
then cleared. I can't help feeling there is just some method I've
never had to use that should so this, such as having the comboboxes
linked, but Office Help and the internet are not bringing up anything
really relevant.

I was reduced to trying to juggle multiple arrays/ranges with events
adding and removing items from each - seemed very nasty, and that
usually means I'm trying to do something the hard way!

Can this be done without hundreds of lines of code and lots of ugly
updates to a data sheet? Or am I barking up the wrong tree and is
there another, better way of achieving much the same result?

 

[RB Smissaert]

I don't quite understand the question/problem, but I have a feeling that a
simple collection would do the job.

RBS

 

[gordon.moar]

I don't quite understand the question/problem, but I have a feeling that a
simple collection would do the job.

Sorry for the ropey explanation, I know how hard it can be to
visualise other peoples' problems!

Could you elaborate on how you think a 'simple collection' might
suffice? I'm aware of using objects in collections, but don't think I
am aware of how they might be applied in this instance.

 

[gordon.moar]

Perhaps it would help if I tried to illustrate my problem.

Create a form with two comboboxes; combobox1 & combobox2.
Paste this into the form module:

Private Sub UserForm_initialize()
Dim myArray(3) As String
myArray(0) = "Dave"
myArray(1) = "Mary"
myArray(2) = "Jane"
myArray(3) = "John"
ComboBox1.List = myArray
ComboBox2.List = myArray
End Sub

What I want is if I select, say, "Dave" in Combobox1, then I want
myArray to be repopulated so that "Dave" no longer appears as an
option in combobox2. If I then blank combobox1, "Dave" should be re-
added to myArray meaning it will be available for selection elsewhere.

As I said, I may be on completely the wrong boat with this one and I'm
sure there is an easy way of managing this....I just haven't been
shown the path yet!

 

[RB Smissaert]

Still not 100% clear, but see if this works.
All in the form module.

Option Explicit
Private arrNames

Private Sub UserForm_initialize()

arrNames = Array("Dave", "Mary", "Jane", "John")

ComboBox1.List = arrNames
ComboBox2.List = arrNames

End Sub

Private Sub ComboBox1_Click()

Dim i As Long

For i = 0 To ComboBox2.ListCount - 1
If ComboBox2.List(i) = ComboBox1.Value Then
ComboBox2.RemoveItem i
Exit For
End If
Next i

End Sub

Private Sub ComboBox1_Change()

If ComboBox1.ListCount = 0 Then
ComboBox2.List = arrNames
End If

End Sub

Private Sub CommandButton1_Click()
ComboBox1.Clear
End Sub


RBS

 

[gordon.moar]

Still not 100% clear, but see if this works.

Almost. This manages what I have been able to achieve myself;
selecting something in one box removes that from the available values
in another. What I'm struggling with is the restoring of individual
entries across multiple comboboxes, maintaining a consistent list of
'unused' values across each.

Although your method kinda works with two controls, when I say that
I'm trying to use nine, with a list of values that will vary at
runtime, you can maybe see why things started to get messy with this
kind of approach, leading me to think there must be a better way.

Thanks for your input so far though.

 

[Bernd P]

Hello,

Maybe my UDF lfreq can serve as an example:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[gordon.moar]

Maybe my UDF lfreq can serve as an example:http://www.sulprobil.com/html/listfreq.html

Thanks Bernd, looks great and you obviously know your stuff, but I'm
afraid my puny Earth brain can't relate your UDFs to my problem! How
might this help?