Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Boxes in Excel 2000 (Excel 2000)

    I have created a set of combo boxes that point to various link cells.

    I want to create a second set of combo boxes that display lists that are chosen based on the values of the first combo boxes. For instance, if the user selects the 2nd option in combo box 1, the link cell displays a "2". I would like the next combo box then to read the "2" and select a range of cells which contain the drop-down text I would like the user to be able to select from for the second box.

    The first combo-boxes are easy. It's creating the second set, that display text based on the choice of the first box, that has me stumped. Is there a way to do this?

    Thanks in advance for your help!

    Richard H

  2. #2
    New Lounger
    Join Date
    May 2002
    Location
    Colorado, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Hi Clone of Struve,

    Linking comboboxes in this way is quite easy. For an example, let's say your first combobox is ComboBox1 and has the list options "North", "South", "East" and "West".

    Now lets say that the list of offices in the North region is in cells c6:c9, the South in d6:d9, etc. The following code in the appropriate event code module will link the comboboxes:

    Private Sub ComboBox1_Change()
    With ComboBox2
    Select Case ComboBox1.Text
    Case "North": .ListFillRange = "c6:c9"
    Case "South": .ListFillRange = "d6:d9"
    Case "East": .ListFillRange = "e6:e9"
    Case "West": .ListFillRange = "f6:f9"
    End Select
    End With
    End Sub

    To get to the correct event code module, simply double click on ComboBox1 while in the Design Mode.
    _________________
    Keep Excelling.

    Damon

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Damon's reply applies to combo boxes created from the Control Toolbox. My impression was that you created them from the Forms toolbar; if so, take a look at the attached workbook. The code behind the first combo box is a macro in Module1.

  4. #4
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Thank you, Hans. This was EXACTLY what I was looking for!

    Richard

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Thank you, Damon.

    As Hans points out, I had created the boxes with the Forms toolbar, but your idea is great and I will save it for future use.

    Richard H

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Hans, I tried pasting your code into my spreadsheet after testing it in the sample you created. It works fine in your sample, but crashes in my worksheet-- in particular, the line where you say 'Set rng = ActiveSheet.Range......"

    The only change I made to the code you wrote was to rename the macro-- from cboCountry_CHange to cboPrograms_Matrix.

    I do not know why it works fine in your sample but does not work in my sheet. Would you like me to send you a copy of my sheet to look at?

    Richard H

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    You can attach your workbook to a reply. If it is too large (the limit is 100 KB), create a copy, strip away as much as possible, and if necessary zip the result. Oh, and remove sensitive information, or replace it with dummy data.

  8. #8
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    The sample worksheet is attached. Thank you so much for your assistance!

    Richard H

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Below the last line of the macro there is a second, superfluous End Sub. If you remove it, the combo boxes should work OK.

  10. #10
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    As we say in the States, Hans--- Duh!

    Thanks for the help. I really appreciate it. Works fine now!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Boxes in Excel 2000 (Excel 2000)

    Lounger <!profile=mn1950>mn1950<!/profile> asked me in a PM how the example should be changed if the list fill ranges for the combo boxes are on a different sheet. Since others may have the same question, I post the reply here.

    The code in the demo workbook refers to ActiveSheet several times. Where appropriate, this must be changed to the worksheet containing the lists. For example, if the lists are on a sheet named Sheet2, the On Change code for the first combo box becomes

    Sub cboCountry_Change()
    Dim rng As Range, rng2 As Range
    Dim wsh As Worksheet
    Dim strVal As String
    Set wsh = Worksheets("Sheet2")
    Set rng = wsh.Range(ActiveSheet.Shapes(1).ControlFormat.List FillRange). _
    Cells(ActiveSheet.Shapes(1).ControlFormat.ListInde x)
    strVal = rng.Value
    Set rng = wsh.Range("F1:F10").Find(strVal)
    Set rng2 = rng
    Do While rng2.Value = rng.Value
    Set rng2 = rng2.Offset(1, 0)
    Loop
    Set rng = wsh.Range(rng.Offset(0, 1), rng2.Offset(-1, 1))
    With ActiveSheet.Shapes(2).ControlFormat
    .ListFillRange = rng.Address(External:=True)
    .ListIndex = 0
    End With
    End Sub

    There is a new variable wsh of type Worksheet. It is set to refer to Sheet2, then used in several places in the code. The modified demo workbook is attached.

Posting Permissions

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