E
Ed from AZ
I need to create a header row in a new worksheet based on user
choices. I have all the possible choices in one "master" row - each
cell is already formatted for color, border, column width, font, etc,
and the program will take the ones chosen by the user and copy them
onto a new sheet. Data will later be added, so getting the proper
headers up front is a bit important.
The individual column headings can be separated into a couple of
different data categories, and there is always the possibility that a
user will want choices not yet there that fit a different category.
The question I have is how to list all these headings in different
columns based on their data category. The data category for each
individual heading would be contained in a comment in that cell.
The complete composite header row stretches from A1 to AD1 at the
moment; more will likely be added later. I'd like to go to CA1, put
the data category name, and write all headers for that category in CA2
and down; go to CC1, put the next category name, all headers for that
category in CC2 and down; lather, rinse, repeat for all categories.
All this would be in a Workbook_Open sub.
My general fuzzy idea goes something like this:
-- Iterate through the cells of the whole header row.
-- Read the comment text of each cell to get the category.
-- Write the text of the header cell in the column for the proper
category.
The tough part is not knowing how many categories there are or what
their names are, because things will get added as we go along. That
means I have to track which categories have been created; if this next
one has not been created yet, then go to the next column over and
create it, then write the header under it. (The user will then scan
these lists, put an X by the desired headers, and click a button to
build the desired header row n a new worksheet. That part I think I
can handle.)
Do I create an array variable on the fly for each category, and then
iterate through all my arrays to write each one to the worksheet? Is
there a better way to track "categories created" so I know when I hit
a new one? How would y'all approach this?
Ed
(Working in XL2003)
choices. I have all the possible choices in one "master" row - each
cell is already formatted for color, border, column width, font, etc,
and the program will take the ones chosen by the user and copy them
onto a new sheet. Data will later be added, so getting the proper
headers up front is a bit important.
The individual column headings can be separated into a couple of
different data categories, and there is always the possibility that a
user will want choices not yet there that fit a different category.
The question I have is how to list all these headings in different
columns based on their data category. The data category for each
individual heading would be contained in a comment in that cell.
The complete composite header row stretches from A1 to AD1 at the
moment; more will likely be added later. I'd like to go to CA1, put
the data category name, and write all headers for that category in CA2
and down; go to CC1, put the next category name, all headers for that
category in CC2 and down; lather, rinse, repeat for all categories.
All this would be in a Workbook_Open sub.
My general fuzzy idea goes something like this:
-- Iterate through the cells of the whole header row.
-- Read the comment text of each cell to get the category.
-- Write the text of the header cell in the column for the proper
category.
The tough part is not knowing how many categories there are or what
their names are, because things will get added as we go along. That
means I have to track which categories have been created; if this next
one has not been created yet, then go to the next column over and
create it, then write the header under it. (The user will then scan
these lists, put an X by the desired headers, and click a button to
build the desired header row n a new worksheet. That part I think I
can handle.)
Do I create an array variable on the fly for each category, and then
iterate through all my arrays to write each one to the worksheet? Is
there a better way to track "categories created" so I know when I hit
a new one? How would y'all approach this?
Ed
(Working in XL2003)