Consulting

Results 1 to 9 of 9

Thread: Conditionally Reducing Choices in Multiple Combo Boxes

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    5
    Location

    Unhappy 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....

    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

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    How about using something like this for the combobox1.change

    ComboBox2.RemoveItem (ListBox1.ListIndex)

  3. #3
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  4. #4
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    5
    Location

    Thumbs up Thanks for your help

    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

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You can attach the file. Just zip the excel workbook and attach it and I or someone else will have a look at it.

  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    5
    Location

    Talking OK guys - it works - thanks. One more question though!!

    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.

    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.


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

    Regards,

    Paul

  8. #8
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  9. #9
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    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.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •