Combo box using multiple table lists

[jarhead58]

Hello all!

I have 2 combo boxes, first one has a choice of 3 people. When one of them is chosen, I want the 2nd combo box to list items from that persons table/cell range. Lets say I chose 1 of a,b, or c in the first box.
Each choice has their own list to be used. A uses table choice1, B choice2 and so on. I tried using vba to just assign the rowsource after the "Change" in the first box but it doesn't work. What am I missing?
I was using:

combobox2.rowsource = "choice1"

Using choice2 and choice3 as needed. TIA

 

[jarhead58]

Combobox

 

[Fluff]

In that case what are the ranges the you need to put into the combo?

 

[jarhead58]

C4:C100, H and M same range

 

[jarhead58]

What is Choice1? a named range or the name of a combobox?

A combobox

 

[Fluff]

In that case how about, something like

Private Sub ComboBox1_Click()
   Select Case Me.ComboBox1.Value
      Case "A"
         Me.ComboBox2.List = Sheets("Sheet1").Range("C4:C100").Value
      Case "B"
         Me.ComboBox2.List = Sheets("Sheet1").Range("H4:H100").Value
      Case "C"
         Me.ComboBox2.List = Sheets("Sheet1").Range("M4:M100").Value
   End Select
End Sub

 

[jarhead58]

In that case how about, something like

Private Sub ComboBox1_Click()
   Select Case Me.ComboBox1.Value
      Case "A"
         Me.ComboBox2.List = Sheets("Sheet1").Range("C4:C100").Value
      Case "B"
         Me.ComboBox2.List = Sheets("Sheet1").Range("H4:H100").Value
      Case "C"
         Me.ComboBox2.List = Sheets("Sheet1").Range("M4:M100").Value
   End Select
End Sub

Still nothing. Its almost like it's not even getting to that point.

 

[Fluff]

How are you calling the code?

 

[jarhead58]

How are you calling the code?

Here's the code that I use. I tried your select case and then commented them out to test the other.

Private Sub Bills_Click()
'    Select Case Me.Who.Value
'      Case "Frank"
'         Me.Bills.List = Sheets("Sheet1").Range("C4:C100").Value
'      Case "Janie"
'         Me.Bills.List = Sheets("Sheet1").Range("H4:H100").Value
'      Case "Joe"
'         Me.Bills.List = Sheets("Sheet1").Range("M4:M100").Value
'   End Select
    If Who.Value = "Frank" Then
        Bills.RowSource = Sheet1.Range("C4:C100")
        UserID.Value = Application.WorksheetFunction.VLookup(Bills.Value, Sheets("sheet1").Range("FLookup"), 2, 0)
    'End If
        If Who.Value = "Janie" Then
            Bills.RowSource = "H4:H100"
            UserID.Value = Application.WorksheetFunction.VLookup(Bills.Value, Sheets("sheet1").Range("JLookup"), 2, 0)
        End If
            If Who.Value = "Joe" Then
                Bills.RowSource = "Sheet1!M4:M100"
                UserID.Value = Application.WorksheetFunction.VLookup(Bills.Value, Sheets("sheet1").Range("JoeLookup"), 2, 0)
            End If
End Sub

 

[Fluff]

assuming that Who is another combo, then you need to put the code in the Who_Click event.

 

[jarhead58]

assuming that Who is another combo, then you need to put the code in the Who_Click event.

That got it! Had some minor tweaking but working great!! Thank you for all the help!