PDA

View Full Version : Solved: Conditionally Reducing Choices in Multiple Combo Boxes



hightime
06-08-2004, 07:51 AM
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....

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

Jacob Hilderbrand
06-08-2004, 08:31 AM
How about using something like this for the combobox1.change

ComboBox2.RemoveItem (ListBox1.ListIndex)

mark007
06-08-2004, 09:32 AM
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:

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:


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


:)

hightime
06-09-2004, 07:39 AM
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 :)

Jacob Hilderbrand
06-09-2004, 07:43 AM
You can attach the file. Just zip the excel workbook and attach it and I or someone else will have a look at it.

mark007
06-09-2004, 08:58 AM
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:


Private Sub ComboBoxx_Enter()
AddItems x
End Sub


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

:)

hightime
06-10-2004, 06:46 AM
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

mark007
06-10-2004, 07:23 AM
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:


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


:)

shades
06-11-2004, 08:54 AM
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.