Combo Boxes in Excel 2000 (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Richard

Reply | Quote

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

Richard

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Richard H

Reply | Quote

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

Reply | Quote

As we say in the States, Hans— Duh!

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

Reply | Quote

As we say in the States, Hans— Duh!

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Lounger mn1950 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.ListFillRange). _
Cells(ActiveSheet.Shapes(1).ControlFormat.ListIndex)
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.

Reply | Quote

Lounger mn1950 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.ListFillRange). _
Cells(ActiveSheet.Shapes(1).ControlFormat.ListIndex)
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.

Reply | Quote

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.

Reply | Quote