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!

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" <nobody@gmail.com> wrote in message 
news:xPgPm.49333$Zu5.7036@newsfe24.iad...
> 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!
> 

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!


"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:%23$fwIohbKHA.2160@TK2MSFTNGP02.phx.gbl...
> 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.
>
>