Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Color (Office Xp)

    My company has a very distinct color scheme and I would like all excel work to be consistent. So I want to change the default color palette in all new workbooks, all existing workbooks, and all Word-embedded excel objects. I understand that color palettes are workbook properties, but there must be VBA code to copy .colors from PERSONAL to any active workbook.

    Any help would be appreciated

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Create a workbook with the colours the way you need them.

    Save this workbook, filetype Template, in the XLSTART directory and call it Book.xlt.
    Delete all but one sheet from it and save this (again as a template and in the same directory) as sheet.xlt.

    Now all new books and all newly inserted sheets should have your colour scheme.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Wichita, Kansas, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Try looking at MS Knowledge Base article Q291293. While it doesn't directly answer your quesion it might put you on the right path.

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Thanks for the reply

    I'm familiar with the book.xlt method for customizing the colors of NEW workbooks, but I'm hoping to find VBA code that will set up my custom colors in existing workbooks as well as embedded workbooks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Color (Office Xp)

    Does this do what you want?
    ActiveWorkbook.Colors = Workbooks("Personal.xls").Colors

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Yes Hans,

    I think that should do the trick, but where do I put that sub so that it will run on every workbook that i open? Do I need to write it as an add-in?

    Thanks in advance

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Color (Office Xp)

    To run code when a specific workbook is opened, put it in the WorkBook_Open event of that workbook.
    To run code whenever a workbook is opened, you must create application-level events.

    You can find general information on application-level events here on Chip Pearson's site (a great source of Excel information).

    There are several examples of application-level events in this forum, for instance in <post#=174803>post 174803</post#>.

    Note: are you sure you want to do this? It would mean that if you open a spreadsheet from outside your company (for instance an attachment in this forum), and it contained a carefully designed color scheme, it would be overridden by your color scheme. An alternative would be to provide toolbar buttons for (a) setting the color scheme in the active workbook, and ([img]/forums/images/smilies/cool.gif[/img] batch conversion of all workbooks in a folder.

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Thanks,

    The toolbar button was just the ticket.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Color (Office Xp)

    Hi Hans,

    <<ActiveWorkbook.Colors = Workbooks("Personal.xls").Colors>>

    Nice. I learnt something new again today.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •