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

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
>

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