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

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

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