Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically changing default formatting upon opening new workbook
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 Auto_Open() 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 Auto_Open() 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically changing default formatting upon opening new workbook
First, I've never seen an Auto_Open 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. On 08/04/2010 08:28, Sam Adams wrote: 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 Auto_Open() 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 Auto_Open() 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically changing default formatting upon opening newworkbook
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close form automatically upon opening workbook | Excel Discussion (Misc queries) | |||
Personal workbook not opening automatically | Excel Discussion (Misc queries) | |||
Excel Automatically Changing Decimals to Text as Default | Excel Discussion (Misc queries) | |||
Opening workbook automatically as Read-0nly | Excel Programming | |||
automatically opening workbook | Excel Discussion (Misc queries) |