XL's Templates don't attach to workbooks. Rather they are copied whole to make a new workbook. This means that if you have a Workbook_Open() macro in your template to set the position, size and zoom of your workbook's window, it will be copied to your workbook as well. This is often not desirable, since colleagues may not appreciate your settings as much as you do.
If you always want workbooks to open in a certain way (by default, you can still change them after opening), you can add an event class handler to your Personal Macro Workbook (or another workbook in your Microsoft Office N:Office:Startup:Excel: folder).
Type Opt-F11 to switch to the Visual Basic Editor (VBE). Insert a new class module , rename it MyWindowClass by choosing View/Properties, and changing the text after (Name) in the Properties window. Paste the following in the class module (modify to suit, of course):
Option Explicit
Public WithEvents MyWindow As Application
Private Sub MyWindow_WorkbookOpen(ByVal Wb As Excel.Workbook)
SetWindow
End Sub
Private Sub SetWindow()
With ActiveWindow
.Top = 0
.Left = 0
.Height = Application.UsableHeight 'or # of pixels
.Width = Application.UsableWidth 'or # of pixels
.Zoom = 100
End With
End Sub
(I like to use .Height = Application.UsableHeight - 50 to allow some room around the sheet to click on the desktop). Then put the following in the ThisWorkbook code module of your Personal Macro Workbook:
Dim objMyWindow As New MyWindowClass
Private Sub Workbook_Open()
Set objMyWindow.MyWindow = Application
End Sub
Now, anytime a workbook opens, the code in your class module will be called to position, resize, or set the zoom for that window.
This page last updated Sunday, 30 November 2003
© Copyright 2001 - 2003 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.