Drop Box Row Source

[TKB]

Hello,

I am currently creating a form that will allow the user to select a portfolio, and then select an item in that portfolio using drop down menus. I have the Sheet being accessed set up in the following manor:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Portfolio List[/TD]
[TD]Portfolio 1[/TD]
[TD][/TD]
[TD]Portfolio 2[/TD]
[TD][/TD]
[TD]Portfolio 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Portfolio 1[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Portfolio 2[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Portfolio 3[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD][/TD]
[TD]Item 4[/TD]
[/TR]
</tbody>[/TABLE]

Now, i already have the portfolios listed in Column A (as shown) which is sourced to ComboBox1(aka Portfolio List). When the user selects the desired portfolio, the macro automatically searches Row 1 for the appropriate Portfolio and selects the associated cell.

What i need to figure out is, how do i set the rowsource for ComboBox2(aka Item List) to the items only associated with the selected portfolio instead of creating one really long list of items and having the user sift through them.

Additional Information:

• There will never be a portfolio with more than 100 Items
• I have tried to use the ActiveCell.Offset(Row,Column).Select a couple of different ways. (eg. Rowsource = Sheet!ActiveCell.Offset(1,1):ActiveCell.Offset(100,1))

Any ideas would be great, and if you need additional information please ask, I am usually fairly good and researching and finding the answer, but this one seems to be eluding me.

Thanks for any help you can offer,

Trey

 

[AlphaFrog]

Can you post your code that shows how ComboBox1 is populated and also how ComboBox 2 is currently populated even though it only works for the one Portfolio.

In your example data, why are the items in a different column than the column header. Portfolio1 is in B1 and it's items are in C2:C5?

 

[TKB]

Can you post your code that shows how ComboBox1 is populated and also how ComboBox 2 is currently populated even though it only works for the one Portfolio.

In your example data, why are the items in a different column than the column header. Portfolio1 is in B1 and it's items are in C2:C5?

Thank you for responding AlphaFrog, First let me answer your second question:

The only reason that it is shown this way is because of personal preference on how it shows. It doesnt really matter if Portfolio 1 is in B1 and its items are in B2:B5. If it needs to be changed, thats not a big deal, its just a simple "ActiveCell.Offset" change (instead of "1,1" it would be "1,0"). This is because it is populated via another form which asks you to input the items (up to 30 to start). Once you click the "button" associated with that part of the form, it locates the correct Portfolio, and then begins listing the items in the cell which is Offset(1,1) from the Portfolio name. (I hope this makes sense)

Anyway, as for the Code:

ComboBox1 (This is a simple pre-defined by me rowsource)

'Folders and Documents'!A2:A100

ComboBox2 (This is where i need the rowsource to select the correct set of items)

'First, make sure the correct sheet is being used
If Not Sheets("Folders and Dcouments").Select Then

Sheets("Folders and Documents").Select


Else


End If

'Set the starting cell as "A1"

Range("A1").Select


ActiveCell.Offset(0, 2).Select

'Search for correct Portfolio (as selected in ComboBox1)

Do Until ActiveCell.Value = ComboBox1.Text Or ActiveCell.Value = ""


ActiveCell.Offset(0, 2).Select


Loop


If ActiveCell.Value = "" Or Not ComboBox1.Text Then


MsgBox "We apologize, We cannot find this portfolio, please contact the Developer", vbOKOnly, "Error"


Else

'Select the first item in the item list for that portfolio

ActiveCell.Offset(1, 1).Select

'HELP!!!!!!

ComboBox2.RowSource = ActiveCell.Select:ActiveCell.Offset(100,0)
ComboBox2.RowSource

Again, thank you for responding and helping me on this!

Trey

 

[AlphaFrog]

I didn't follow your explanation of the data.

If you can configrue the data like below, you could then use the headers in row 1 to populate ComboBox1. Then use the Index of the selected value as the column offset to populate ComboBox2

Excel Workbook
	A	B	C
1	Portfolio 1	Portfolio 2	Portfolio 3
2	P1 Item 1	P2 Item 1	P3 Item 1
3	P1 Item 2	P2 Item 2	P3 Item 2
4	P1 Item 3	P2 Item 3	P3 Item 3
5	P1 Item 4	P2 Item 4	P3 Item 4
Sheet

Private Sub UserForm_Initialize()
    ComboBox1.List = Application.Transpose(Sheets("Folders and Documents").Range("A1:C1").Value)
End Sub

    
Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then
        ComboBox2.Value = ""
        ComboBox2.List = Sheets("Folders and Documents").Range("A2:A5").Offset(, ComboBox1.ListIndex).Value
    Else
        ComboBox2.Clear
    End If
End Sub

 

[TKB]

Hey AlphaFrog,

Thanks for getting back to me. I tried to insert the code and it gets stuck on

[LEFT][COLOR=#333333] ComboBox1.List = Application.Transpose(Sheets("Folders and Documents").Range("A1:C1").Value)

I get an error message saying "Access Denied". It might have something to do with the Application.Transpose function? I dont know this function well enough to understand what it does.

Thanks![/COLOR][/LEFT]

 

[AlphaFrog]

Application.Transpose​

just transposes columns to rows

Try clearing the Rowsource property for ComboBox1

 

[TKB]

Thats precisely what i forgot to do. Thanks for all your help Alpha, you just solved a big headache for me!