Excel VBA - combobox list that is indirect

Hi, I put two comboboxes on a user form ComboBox1 & ComboBox2 ComboBox1 was populated with the values 1, 2 & 3 from cells D3 to D5 in the form's initialization code (you could use a named range instead). In the Change event for ComboBox1 there is a Select Case ... End Select structure to respond to the selection, 1, 2 or 3 in this example. The code then populates ComboBox2 with one of the three named ranges I had created - User1, User2 and User3. Here is the code in the UserForm: Private Sub UserForm_Initialize() 'populate combobox 1 & display first item Me.ComboBox1.RowSource = "D3:D5" Me.ComboBox1.ListIndex = 0 End Sub Private Sub ComboBox1_Change() 'respond to selected value and populate combobox 2 Select Case ComboBox1.Value Case 1 Me.ComboBox2.RowSource = "User1" Case 2 Me.ComboBox2.RowSource = "User2" Case 3 Me.ComboBox2.RowSource = "User3" End Select 'display first value in list Me.ComboBox2.ListIndex = 0 End Sub You don't need to use an 'Indirect' method in VBA and the named ranges do not have to be the same as the text in the first drop-down. Regards

Hi, I tried this but I was getting an error when it came to the part: Me.ComboBox2.ListIndex = 0 Also, my combobox1 (cmbAcctRepNames) has 55 reps and on another named range I have 436 stores that I've named by Rep so in a regular worksheet I could use INDIRECT and just the stores for that rep would be available in combobox2 (lststoreno). But, in userform the combobox2 won't let me use INDIRECT. So, I just did a name range for all the stores and put it in the ROWSOURCE field. Which is okay but I really would like only the stores that apply to the REP when the Rep picks his name in combox1. I've looked online and can't seem to find any info on it. thanks so much! :)

Hi, The sample code I supplied allows you to get lists for each Rep using named ranges. It does the same as using INDIRECT on a worksheet dropdown. I suggest you post the code you have used that responds to changes in the first Combobox - the one that selects the Rep. In my example selecting a Rep in Combobox1 will fill Combobox2 with a list that applies to the selected Rep. Regards

Sorry, I had to work on something else and am just now getting back to this. Thank you so much for your help! Private Sub UserForm_Initialize() 'populate CmbAcctRepName & display first item Me.CmbAcctRepName.RowSource = "AccountRepsName" Me.CmbAcctRepName.ListIndex = 0 End Sub Private Sub CmbAcctRepName_Change() 'respond to selected value and populate liststoreno Select Case CmbAcctRepName.Value Case 1 Me.liststoreno.RowSource = "Alexander_Jerry" Case 2 Me.liststoreno.RowSource = "Alexander_Tina" Case 3 Me.liststoreno.RowSource = "Ashenfelter_Triesta" End Select 'display first value in list Me.liststoreno.ListIndex = 0 (THIS IS WHERE I'M GETTING ERROR) End Sub Also, I am assuming that I have to list all 55 names as I did the 1st 3 names? Thanks again.

Hi, I suspect that the problem is in the section before the line that causes the error. Your Select Case starts off with: Select Case CmbAcctRepName.Value This means that Select will respond to the Value from the Select Case CmbAcctRepName drop down. As this was populted from the "AccountRepsName" named range, I assume that the drop-down is a list of names. Hence the value used by Select is a name. Each Case statement must therefore by a name: If the name selected was: Alexander_Jerry Then you need a section like this: Case Alexander_Jerry Me.liststoreno.RowSource = "Alexander_Jerry" My example was confusing - I had populated the first drop-down with the numbers 1, 2 & 3, hence I used Case 1, Caase 2 & Case 3. I should have used names and it would have been clearer for you. To get 55 names and Case statements, try this: If your list of rep names is in column C, starting at cell C2, enter this in D2: =CHAR(10) & "Case " &C2 & CHAR(10) & "Me.liststoreno.RowSource =" & C2 Drag it down alongside all 55 names. Now select the 55 cells in column D and Copy them. Go to the VB window and after Select Case CmbAcctRepName.Value paste in the data. You will get some extra double quotes, so select what you have just pasted and from the VB menu bar select Edit - Replace ... and replace " with nothing. Complete your Select Case with End Select Regards

Hi Humar and thanks for your huge help. However, when I try your code I get an error message: Runtime error '380': Could not set the ListIndex property.Invalid Property Value and it happens in line: "Me.ComboBox2.ListIndex = 0" Any ideas how to fix this? Many thanks in advance. Michael