Combo box using multiple table lists

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
329
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:
VBA Code:
combobox2.rowsource = "choice1"
Using choice2 and choice3 as needed. TIA
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,149
Office Version
  1. 365
Platform
  1. Windows
In that case what are the ranges the you need to put into the combo?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,149
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case how about, something like
VBA Code:
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

Active Member
Joined
Sep 21, 2017
Messages
329
In that case how about, something like
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,149
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How are you calling the code?
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
329
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,149
Office Version
  1. 365
Platform
  1. Windows
assuming that Who is another combo, then you need to put the code in the Who_Click event.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,014
Messages
5,599,345
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top