Automatically changing default formatting upon opening new workbook

[Sam Adams]

I am trying to write a macro that will change the formatting for
colors and font upon opening a new excel workbook. [We are
implementing brand standards that may change in the future]

I've tried the Autpen() method and it doesn't work because it runs
the macro prior to a workbook being active.

Additionally, I want this macro to work properly if opening up an old
document.

Here is the outline of what I have so far: (sorry, never posted
before)

Sub Autpen()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.Colors(53) = RGB(152, 0, 46)
ActiveWorkbook.Colors(52) = RGB(226, 126, 26)
ActiveWorkbook.Colors(51) = RGB(255, 192, 65)
ActiveWorkbook.Colors(49) = RGB(27, 117, 91)
ActiveWorkbook.Colors(11) = RGB(27, 44, 117)

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I'm guessing I'm not familiar with some command, but I typically use
ActiveWorkbook. Please help me out.

Thanks so much,
Sam

 

[Dave Peterson]

First, I've never seen an Autpen like this fail because the workbook isn't
active yet. (I've seen other procedures fail, though...)

I'd try using:
Thisworkbook
instead of
ActiveWorkbook
to see if that helped.

But if you use the auto_open procedure, you'll need to put this code into every
workbook that needs it.

You have other options.

You could create a workbook template (*.xlt or *.xltm or *.xltx) that could be
used as the basis for any new workbook.

But that won't help any existing workbook. But in my experience, I wouldn't
want something that would change the colors of every workbook I open.

I'd be afraid that if I opened a workbook that belonged to someone else and had
to return it, then I'd be the person responsible for a change the original
author didn't want. (Even departments within the same company may not want
existing workbooks touched!)

Personally, I'd use the template technique and also create a dedicated macro
that would do the work for existing workbooks -- but only when I initiated that
macro.

But if you want....

You could create an addin (so it'll be hidden from the users).

Have them install it in their XLStart folder so that it opens each time excel opens.

This is the code that you'd place into the ThisWorkbook module of that addin:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal wb As Workbook)
Call DoTheWork(wkbk:=wb)
End Sub
Private Sub xlApp_WorkbookOpen(ByVal wb As Workbook)
Call DoTheWork(wkbk:=wb)
End Sub
Private Sub DoTheWork(ByVal wkbk As Workbook)
With wkbk
.Colors(53) = RGB(152, 0, 46)
.Colors(52) = RGB(226, 126, 26)
.Colors(51) = RGB(255, 192, 65)
.Colors(49) = RGB(27, 117, 91)
.Colors(11) = RGB(27, 44, 117)
End With
End Sub


Again, these kinds of "do all the workbooks no matter what" routines scare me.
I wouldn't use them.

I am trying to write a macro that will change the formatting for
colors and font upon opening a new excel workbook. [We are
implementing brand standards that may change in the future]

I've tried the Autpen() method and it doesn't work because it runs
the macro prior to a workbook being active.

Additionally, I want this macro to work properly if opening up an old
document.

Here is the outline of what I have so far: (sorry, never posted
before)

Sub Autpen()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.Colors(53) = RGB(152, 0, 46)
ActiveWorkbook.Colors(52) = RGB(226, 126, 26)
ActiveWorkbook.Colors(51) = RGB(255, 192, 65)
ActiveWorkbook.Colors(49) = RGB(27, 117, 91)
ActiveWorkbook.Colors(11) = RGB(27, 44, 117)

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I'm guessing I'm not familiar with some command, but I typically use
ActiveWorkbook. Please help me out.

Thanks so much,
Sam

 

[Sam Adams]

Thank you so much, I really appreciate the help as well as your
commentary on some of the dangers of my approach. I like your add-in
idea a lot more.