Drop Box Row Source

TKB

Board Regular
Joined
Aug 18, 2011
Messages
128
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:

ABCDEFG
1Portfolio ListPortfolio 1Portfolio 2Portfolio 3
2Portfolio 1Item 1Item 1Item 1
3Portfolio 2Item 2Item 2Item 2
4Portfolio 3Item 3Item 3Item 3
5Item 4Item 4Item 4

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
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

Board Regular
Joined
Aug 18, 2011
Messages
128
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)
Code:
'Folders and Documents'!A2:A100

ComboBox2 (This is where i need the rowsource to select the correct set of items)
Code:
'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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
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
ABC
1Portfolio 1Portfolio 2Portfolio 3
2P1 Item 1P2 Item 1P3 Item 1
3P1 Item 2P2 Item 2P3 Item 2
4P1 Item 3P2 Item 3P3 Item 3
5P1 Item 4P2 Item 4P3 Item 4
Sheet



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

Board Regular
Joined
Aug 18, 2011
Messages
128

ADVERTISEMENT

Hey AlphaFrog,

Thanks for getting back to me. I tried to insert the code and it gets stuck on
Code:
[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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
Application.Transpose
just transposes columns to rows

Try clearing the Rowsource property for ComboBox1
 

TKB

Board Regular
Joined
Aug 18, 2011
Messages
128
Thats precisely what i forgot to do. Thanks for all your help Alpha, you just solved a big headache for me!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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