Excel VBA - combobox list that is indirect

July 21, 2010 at 08:24:42
Specs: Windows XP

Hi, I have 2 combobox list in my userform
1) Persons Name
2) Stores assigned to that Person

I know how to have the 2nd dropdown box be indirect from the 1st dropdown list in a regular worksheet. I am new to VBA and don't know how to make my combobox2 be indirect to combobox1 in a userform. Also, my list's have name ranges.


See More: Excel VBA - combobox list that is indirect

Report •


#1
July 21, 2010 at 09:57:39

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


Report •

#2
July 22, 2010 at 05:27:26

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! :)


Report •

#3
July 22, 2010 at 06:26:15

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


Report •

Related Solutions

#4
July 26, 2010 at 11:37:17

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.


Report •

#5
July 28, 2010 at 13:04:59

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


Report •

#6
October 27, 2010 at 04:25:26

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


Report •


Ask Question