Select Cases and Comboboxes

**AnthonyWB** · 01-12-2011, 05:54 PM

Hello everyone. I have an outstanding problem that I could use some help on.

I have a userform that contains two comboxes. If the user selects value "A" in the first combobox, I want a specified range in a spreadsheet to be used as the rowsource for the second combobox.

If the user selects value "B" in the first combobox, I want a different specified range in a spreadsheet to be used as the rowsource for the second combobox.

Below is the source code.

Option Explicit

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Select Case ComboBox1.Value
    Case Range("A1")
        ComboBox2.RowSource = "TrackerManagement!C2:C10"
    Case Range("A2")
        ComboBox2.RowSource = "TrackerManagement!D1:D10"
End Select

End Sub

Now this code is in the actual userform itself. A seperate module "Module 3" shows the form :

Option Explicit
Sub CommandButton31_Click()
    Application.ScreenUpdating = False
    TrackerManagement.Show
End Sub

it's not working, and I cannot figure it out. Any help is greatly appreciated.

**AnthonyWB** · 01-12-2011, 06:24 PM

Ok, it sort of works now with the following code:

Option Explicit

Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

Sheets("TrackerManagement").Select
Select Case ComboBox1.Value
    Case Range("A1")
        ComboBox2.RowSource = "TrackerManagement!C2:C10"
    Case Range("A2")
        ComboBox2.RowSource = "TrackerManagement!D1:D10"
End Select

End Sub

The problem now is quite wierd. When the user selects a value in the first combobox, correct values are loaded into the second combobox however when you press the down arrow to display the values it comes up behind the other combobox.

**MarvinP** · 01-12-2011, 06:28 PM

Hi Anthony,

I think you need to use

Case A
Case B

instead of

Case Range("A1")
Case Range("B1")

If that doesn't work try

Case Range("A1").Value
Case Range("B1").Value

See examples on http://www.ozgrid.com/VBA/select-case.htm

**MarvinP** · 01-12-2011, 07:25 PM

After searching the net for a long time it was easier to build a short example

See attached.

**MarvinP** · 01-12-2011, 11:13 PM

Here is the Case way to do this problem

Private Sub ComboBox3_Change()
    Select Case ComboBox3.Text
        Case Is = "A"
            ComboBox2.RowSource = "Sheet1!C2:C7"
        Case Is = "B"
            ComboBox2.RowSource = "Sheet1!e2:e7"
    End Select
End Sub

Who would have guessed we needed "Case Is = " to make the syntax right.