Using VBA to change Excel layouts

[Robert Crandal]

Our company has designed 4 different spreadsheet designs. The same
data gets stored in any of the 4 spreadsheet layouts....the only difference
is that some spreadsheets use different colors, have different fonts and
cell sizes and etc...

I thought it would be nice to create just ONE spreadsheet that contains
a combo-box or list-box or drop-down box which allows the user to
select 1 of the 4 layout choices, then I want VBA to programattically setup
the spreadsheet layout according to the users choice. Does that make sense?

Does anybody know any good ways to implement something like this?? Right
now the only thing I can think of is create a huge "Select Case" statement
which
changes the spreadsheet properties/colors/cell sizes according to the user's
choice.

Please do give feedback. Thank you everyone!

 

[JLGWhiz]

You could make four templates and simply give the user a choice of which
template to use. The template would have to be saved as a different file
name than the template, but if it is a true template file (.xlt), it will
require that the user do that. Then you could use a listbox or combobox
with the four choices and when the user selects, it runs a macro to open the
xelected template.

 

[Robert Crandal]

Well, I was hoping to avoid file open operations. I kind of just
wanted the spreedsheet to transform itself "on the fly" or
immediately when a user makes a listbox selection or something.

So, my current code idea would look as follows:

Select Case spreedsheet_choice
Case 1:
Columns(1).ColumnWidth = 12
Columns(2).ColumnWidth = 12
Columns(3).ColumnWidth = 12
Cells(1, 1).Value = "Name"
Cells(1, 2).Value = "Age"
Cells(1, 3).Value = "Sex"
Cells(1, 1).Interior.ColorIndex = 3
Cells(1, 2).Interior.ColorIndex = 3
Cells(1, 3).Interior.ColorIndex = 3
' etc etc
Case 2:
Columns(1).ColumnWidth = 5
Columns(2).ColumnWidth = 5
Columns(3).ColumnWidth = 5
Cells(1, 1).Value = "Client Name"
Cells(1, 2).Value = "Age"
Cells(1, 3).Value = "M/F"
Cells(1, 1).Interior.ColorIndex = 4
Cells(1, 2).Interior.ColorIndex = 4
Cells(1, 3).Interior.ColorIndex = 4
' etc etc
Case 3:
' etc etc...
Case 4:
' etc etc
End Select


Would the code above be just as effective as your method of loading
a template file??? Is my method above too complicated??

What do you think??

Thank you!