Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Combobox ListFillRange
I have a sheet ("Subs") that contains category names. Under each name is a
list of different numbers (nodes). On a different sheet ("Runs") I have a combobox called "Subsystems" that I want to be filled with the category names in Subs. Since the Subs sheet can change, I want the combobox to update via a Worksheet_Open event. This is easily done if the category names are in a single column, multiple rows. However, I have to have them in a single row, multiple columns. The code below loads the categories, but it loads it into Subsystems as a single entry with multiple columns. Is there a way to transpose the categories in Subs so that the combobox is filled with each column (A1, B1, C1...) is displayed as a seperate entry? Mike WorksheetOpen code excerpt: Set subs = Sheet7.Range("A1") col = subs.CurrentRegion.End(xlToRight).Column Set subs = Range(Cells(1, 1), Cells(1, col)) Sheet8.Subsystem.ListFillRange = "Subs!" & subs.Address |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Combobox ListFillRange
It never fails. You work for hours trying to figure something out. Then the
moment you post it to the board, you see the light. Here's what I did... Sheet8.Subsystem.Clear Set subs = Sheet7.Range("A1") col = subs.CurrentRegion.End(xlToRight).Column For i = 1 To col Sheet8.Subsystem.AddItem Sheet7.Cells(1, i).Value Next "crazybass2" wrote: I have a sheet ("Subs") that contains category names. Under each name is a list of different numbers (nodes). On a different sheet ("Runs") I have a combobox called "Subsystems" that I want to be filled with the category names in Subs. Since the Subs sheet can change, I want the combobox to update via a Worksheet_Open event. This is easily done if the category names are in a single column, multiple rows. However, I have to have them in a single row, multiple columns. The code below loads the categories, but it loads it into Subsystems as a single entry with multiple columns. Is there a way to transpose the categories in Subs so that the combobox is filled with each column (A1, B1, C1...) is displayed as a seperate entry? Mike WorksheetOpen code excerpt: Set subs = Sheet7.Range("A1") col = subs.CurrentRegion.End(xlToRight).Column Set subs = Range(Cells(1, 1), Cells(1, col)) Sheet8.Subsystem.ListFillRange = "Subs!" & subs.Address |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose Combobox ListFillRange
to add a horizontal array to a combo or listbox use the column property (iso the list as you would for vertical arrays) sheet8.subsystem.Column = _ Sheet7.Range("A1", Sheet7.Range("IV1").End(xlToLeft)).Value -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam crazybass2 wrote : It never fails. You work for hours trying to figure something out. Then the moment you post it to the board, you see the light. Here's what I did... Sheet8.Subsystem.Clear Set subs = Sheet7.Range("A1") col = subs.CurrentRegion.End(xlToRight).Column For i = 1 To col Sheet8.Subsystem.AddItem Sheet7.Cells(1, i).Value Next "crazybass2" wrote: I have a sheet ("Subs") that contains category names. Under each name is a list of different numbers (nodes). On a different sheet ("Runs") I have a combobox called "Subsystems" that I want to be filled with the category names in Subs. Since the Subs sheet can change, I want the combobox to update via a Worksheet_Open event. This is easily done if the category names are in a single column, multiple rows. However, I have to have them in a single row, multiple columns. The code below loads the categories, but it loads it into Subsystems as a single entry with multiple columns. Is there a way to transpose the categories in Subs so that the combobox is filled with each column (A1, B1, C1...) is displayed as a seperate entry? Mike WorksheetOpen code excerpt: Set subs = Sheet7.Range("A1") col = subs.CurrentRegion.End(xlToRight).Column Set subs = Range(Cells(1, 1), Cells(1, col)) Sheet8.Subsystem.ListFillRange = "Subs!" & subs.Address |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combobox listfillrange network | Excel Worksheet Functions | |||
Transpose technique not populating ListFillRange of ActiveX combobox | Excel Discussion (Misc queries) | |||
ComboBox ListFillRange | Excel Discussion (Misc queries) | |||
Strange problem with a combobox and ListFillRange | Excel Discussion (Misc queries) | |||
ComboBox - Changing ListFillRange | Excel Programming |