Conditionally Reducing Choices in Multiple Combo Boxes

I wonder if somebody can help me.....

Firstly....I am using Excel 2000 on a Win2000 PC

I have a user form that calls up a combo box to allow a user to make selections from a series of dropdowns (12 in all).

I use ONE range of data and each dropdown box references the SAME range.
It works fine....the user can (say) select box 1, make a selection and do the same for boxes 2-12.

The problem here is that once a user has made a selection from box 1, I do not want that particular choice to be available in the remaining boxes.

As the user goes down through the boxes, the available selections needs to reduce accordingly.

In other (simpler) words the selections need to be exclusive.

For simplicity reasons I only want to have one range to select from as it will be a long list. It will need updating regularly.

I initialise the dropdowns as follows....

Code:

---

Private Sub UserForm_Initialize()
For Each rngcell In Sheets("Data").Range("teamdata")
    ComboBox1.AddItem rngcell.Value
    ComboBox2.AddItem rngcell.Value
    ComboBox3.AddItem rngcell.Value
    ComboBox4.AddItem rngcell.Value
    ComboBox5.AddItem rngcell.Value
    ComboBox6.AddItem rngcell.Value
    ComboBox7.AddItem rngcell.Value
    ComboBox8.AddItem rngcell.Value
    ComboBox9.AddItem rngcell.Value
    ComboBox10.AddItem rngcell.Value
    ComboBox11.AddItem rngcell.Value
    ComboBox12.AddItem rngcell.Value
Next rngcell
End Sub

---

The combobox values are entered into a worksheet on execution of the macro.

Any ideas folks?

I would be most grateful if if somebody can help.

Many thanks in advance

Paul Hodges

How about using something like this for the combobox1.change

Code:

---

ComboBox2.RemoveItem (ListBox1.ListIndex)

---

Hmm, that would only work if theye filled them in in order and never chnged their minds though.

How about something like this:

Have subs like this in the combobox enter events:

Code:

---

Private Sub ComboBox1_Enter()
    AddItems 1
End Sub

Private Sub ComboBox2_Enter()
    AddItems 2
End Sub

---

And add the following sub to the form code module:

Code:

---

Sub AddItems(n As Integer)
Dim strOld As String
    Dim i As Long
    Dim blAdd As Boolean
    Dim r As Range
'store currently selected and clear combo
    With Me.Controls("ComboBox" & n)
    strOld = .Text
    .Clear
    End With
'find the items to display
    For Each r In Sheets("Data").Range("teamdata")
    blAdd = True
    For i = 1 To 12
        If i <> n Then
            If Me.Controls("ComboBox" & i).Text = r.Value Then
                blAdd = False
            End If
        End If
    Next I
    If blAdd Then Me.Controls("ComboBox" & n).AddItem r.Value
Next
'now reselect the previous item
    For i = 0 To (Me.Controls("ComboBox" & n).ListCount - 1)
    If Me.Controls("ComboBox" & n).List(i) = strOld Then
        Me.Controls("Combobox" & n).ListIndex = I
        Exit For
    End If
    Next i
End Sub

---

:)

Mark,

you've put a lot of time into that reply - thanks. I'm a bit new to this so I'm not sure how to integrate that into my full code.

If I post the whole thing here could you suggest how I would include it?


DRJ,
your idea is simpler but maybe with some user issues (pointed out by Mark). Having said that, the user will only pick one at a time and if they change their mind, the form could be cleared and restarted. In other words it could well suffice.
Again, if I post the entire user form VBA could you put it in context for me?

Thanks to you both.
I'm new here and very pleasantly surprised by the effort you have put into helping me. It may be some time before I can return the favour.

Thanks again
Paul :)

You can attach the file. Just zip the excel workbook and attach it and I or someone else will have a look at it.

To add the code remove your userform_initialise code and add the sub Additems above. Then for each combobox you have i.e. all 12 of them add a sub:

Code:

---

Private Sub ComboBoxx_Enter()
    AddItems x
End Sub

---

Where x is the number of the box i.e. 1 to 12.

:)

Thanks to both of you for your help.

Mark07, it seems like your code has done the trick.
I've attached it for your interest (and anyone elses).

Basically I've cut and pasted your work (thanks yet another time). I can't honestly say that I FULLY understand how it works but I'm largely there.:confused:

I've stripped all the maths and other macros etc that I had in the original workbook. It was running at 1.5mb and wasn't a good size to post here. This is JUST the input part of the process.

Interestingly, with this method there seems to be a pause between clicking on a dropdown and waiting for it to actually drop. It must be the time taken for the code to store the data and then read again. It was instantaneous before.
No complaints though, it only offers exclusive data as I asked.

I need some code to re-initialise the form when I click CLEAR. Any ideas? I did get some code for that a while back - couldn't make it work.

Bottom line...you guys really have helped. Both strangers to me and yet you found the time to help. The internet is a wonderful thing. :kiss :) :) :)


I will be happy to a part of this forum and help (in my limited way) in the future.

Regards,

Paul

Glad it works. Yes, the delay is a bit of an issue. It's possible to improve the delay using a different technique, but I don't really have the time to look into it that far - if it works well enough for you though that's all that matters!

:)

As for the UserForm_Initialize code try the following:

Code:

---

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 12
    Me.Controls("ComboBox" & i).Clear
Next i
End Sub

---

:)

Quote:

---

Originally Posted by hightime

Interestingly, with this method there seems to be a pause between clicking on a dropdown and waiting for it to actually drop. It must be the time taken for the code to store the data and then read again. It was instantaneous before.
No complaints though, it only offers exclusive data as I asked.

---

Paul,

I tried your code on my machine (XL2002 on Win2K; 2 GHz, with 680 MB RAM). The dropdown was nearly instantaneous.

Perhaps adding RAM might be a solution. Just thought you might be interested.