How to Maximize an Excel window and set it as preference in Office X

[whitecloud1]

I'm using the early version of Excel (Office X for Mac)

Is it possible to "set preferences" for the Excel window in Ofice X so
that it remembers (when opening files or "new from template') the
exact same location and dimensions of the window containing the data?
I'm using a 12" Powerbook and need to Maximize the visible window and
get it to lock to the top left corner of the screen, i.e. below the
toolbars.

Any help welcome.

John.

 

[JE McGimpsey]

I'm using the early version of Excel (Office X for Mac)

Is it possible to "set preferences" for the Excel window in Ofice X so
that it remembers (when opening files or "new from template') the
exact same location and dimensions of the window containing the data?
I'm using a 12" Powerbook and need to Maximize the visible window and
get it to lock to the top left corner of the screen, i.e. below the
toolbars.

By default, MacOS will open windows at the size and location they were
at when they were closed last. However, you can bypass that.

For "new from template", create a default template with the size and
location you want (see "Create a template for workbooks or worksheets"
in XL Help).

For all workbooks, you can create a class event to resize and reposition
your windows. In my startup add-in (which could be your Personal Macro
Workbook), I create a class module named WindowHandler, and put the
following code in it:

Public WithEvents oApp As Application
Private Const gWindowDefaultTop As Long = 1
Private Const gWindowDefaultLeft As Long = 1
Private Const gWindowDefaultVMargin As Long = 30
Private Const gWindowDefaultHMargin As Long = 200
Private Const gWindowDefaultZoom As Long = 100

Private Sub oApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
SetWindows Wb
End Sub

Private Sub Class_Initialize()
Set oApp = Application
End Sub

Public Sub SetWindows(ByVal wbBook As Excel.Workbook)
Dim wnWindow As Window
For Each wnWindow In wbBook.Windows
With wnWindow
.Top = gWindowDefaultTop
.Left = gWindowDefaultLeft
.Height = oApp.UsableHeight - gWindowDefaultVMargin
.Width = oApp.UsableWidth - gWindowDefaultHMargin
.Zoom = gWindowDefaultZoom
End With
Next wnWindow
End Sub

In a regular code module in the startup add-in I put

Public clsMyWindow As WindowHandler

And in the ThisWorkbook code module of the startup add-in I put:

Private Sub Workbook_Open()
Set clsMyWindow = New WindowHandler
End Sub

The SetWindows sub, which is fired any time a workbook is opened
(whether a new workbook or an existing file), moves the files window(s)
to the top left position, and resizes it, leaving a small margin at the
right and bottom.

 

[whitecloud1]

By default, MacOS will open windows at the size and location they were
at when they were closed last. However, you can bypass that.

For "new from template", create a default template with the size and
location you want (see "Create a template for workbooks or worksheets"
in XL Help).

For all workbooks, you can create a class event to resize and reposition
your windows. In my startup add-in (which could be your Personal Macro
Workbook), I create a class module named WindowHandler, and put the
following code in it:

Public WithEvents oApp As Application
Private Const gWindowDefaultTop As Long = 1
Private Const gWindowDefaultLeft As Long = 1
Private Const gWindowDefaultVMargin As Long = 30
Private Const gWindowDefaultHMargin As Long = 200
Private Const gWindowDefaultZoom As Long = 100

Private Sub oApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
SetWindows Wb
End Sub

Private Sub Class_Initialize()
Set oApp = Application
End Sub

Public Sub SetWindows(ByVal wbBook As Excel.Workbook)
Dim wnWindow As Window
For Each wnWindow In wbBook.Windows
With wnWindow
.Top = gWindowDefaultTop
.Left = gWindowDefaultLeft
.Height = oApp.UsableHeight - gWindowDefaultVMargin
.Width = oApp.UsableWidth - gWindowDefaultHMargin
.Zoom = gWindowDefaultZoom
End With
Next wnWindow
End Sub

In a regular code module in the startup add-in I put

Public clsMyWindow As WindowHandler

And in the ThisWorkbook code module of the startup add-in I put:

Private Sub Workbook_Open()
Set clsMyWindow = New WindowHandler
End Sub

The SetWindows sub, which is fired any time a workbook is opened
(whether a new workbook or an existing file), moves the files window(s)
to the top left position, and resizes it, leaving a small margin at the
right and bottom.

 

[whitecloud1]

By default, MacOS will open windows at the size and location they were
at when they were closed last. However, you can bypass that.

For "new from template", create a default template with the size and
location you want (see "Create a template for workbooks or worksheets"
in XL Help).

For all workbooks, you can create a class event to resize and reposition
your windows. In my startup add-in (which could be your Personal Macro
Workbook), I create a class module named WindowHandler, and put the
following code in it:

Public WithEvents oApp As Application
Private Const gWindowDefaultTop As Long = 1
Private Const gWindowDefaultLeft As Long = 1
Private Const gWindowDefaultVMargin As Long = 30
Private Const gWindowDefaultHMargin As Long = 200
Private Const gWindowDefaultZoom As Long = 100

Private Sub oApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
SetWindows Wb
End Sub

Private Sub Class_Initialize()
Set oApp = Application
End Sub

Public Sub SetWindows(ByVal wbBook As Excel.Workbook)
Dim wnWindow As Window
For Each wnWindow In wbBook.Windows
With wnWindow
.Top = gWindowDefaultTop
.Left = gWindowDefaultLeft
.Height = oApp.UsableHeight - gWindowDefaultVMargin
.Width = oApp.UsableWidth - gWindowDefaultHMargin
.Zoom = gWindowDefaultZoom
End With
Next wnWindow
End Sub

In a regular code module in the startup add-in I put

Public clsMyWindow As WindowHandler

And in the ThisWorkbook code module of the startup add-in I put:

Private Sub Workbook_Open()
Set clsMyWindow = New WindowHandler
End Sub

The SetWindows sub, which is fired any time a workbook is opened
(whether a new workbook or an existing file), moves the files window(s)
to the top left position, and resizes it, leaving a small margin at the
right and bottom.

Thanks for the useful information. Firstly, the window size and
location is not saved by default in my Office X for Mac. This applies
to both Word and Excel.

I'm just about familiar with macros and was able to create a macro for
my existing excel files (all currently saved at 150%) to move the
window, and I save it as "personal macro" . This is a great
improvement. Thank you.


However, I am interested in learning how to set all new files to do
this on opening.
However, I'm not familiar with the terms "class event" or "class
module" or how to create them. Can you please point me where I can
learn how to do what you suggested?

JB

 

[whitecloud1]

Thanks for the useful information. Firstly, the window size and
location is not saved by default in my Office X for Mac. This applies
to both Word and Excel.

I'm just about familiar with macros and was able to create a macro for
my existing excel files (all currently saved at 150%) to move the
window, and I save it as "personal macro" . This is a great
improvement. Thank you.


However, I am interested in learning how to set all new files to do
this on opening.
However, I'm not familiar with the terms "class event" or "class
module" or how to create them. Can you please point me where I can
learn how to do what you suggested?

JB

 

[whitecloud1]

Thanks for the useful information. Firstly, the window size and
location is not saved by default in my Office X for Mac. This applies
to both Word and Excel.

I'm just about familiar with macros and was able to create a macro for
my existing excel files (all currently saved at 150%) to move the
window, and I save it as "personal macro" . This is a great
improvement. Thank you.


However, I am interested in learning how to set all new files to do
this on opening.
However, I'm not familiar with the terms "class event" or "class
module" or how to create them. Can you please point me where I can
learn how to do what you suggested?

JB

OK, I think I did the first 2 things correctly. In Visual Basic do I
really need to indent some lines from the left margin as you did and do
I need to leave blank lines between your paragraphs?
I didn't, and see lines separating the paragraphs.
In the This Workbook ( within Microsoft Excel Objects), I get an error
message opening a new file saying: Compile Error: User-definrd type not
defined. "New WindowHandler" is highlighted as causing the problem. It
seems a small error but can you help?

Best wishes,
JB

 

[ruthlesslightning]

A couple of things:
1) Indentation just makes the code easier to read.
2) However, that can create a problem when you're copying and pasting
someone else's code into your code window, like you're doing. I run
into this problem all the time when I'm copying and pasting code from
someone else's website.
3) The best way I've found to work around that is to keep hitting
Delete until that problematic line is on the line above, then hit
RETURN, then indent accordingly. That solves the problem 90% of the
time for me. Note that you may have to do this more than once.

HTH
Cheers
Geoff Lilley
Microsoft Office Master Instructor, 2000/XP
Apple Certified HelpDesk Specialist
(e-mail address removed)

 

[whitecloud1]

A couple of things:
1) Indentation just makes the code easier to read.
2) However, that can create a problem when you're copying and pasting
someone else's code into your code window, like you're doing. I run
into this problem all the time when I'm copying and pasting code from
someone else's website.
3) The best way I've found to work around that is to keep hitting
Delete until that problematic line is on the line above, then hit
RETURN, then indent accordingly. That solves the problem 90% of the
time for me. Note that you may have to do this more than once.

HTH
Cheers
Geoff Lilley
Microsoft Office Master Instructor, 2000/XP
Apple Certified HelpDesk Specialist
(e-mail address removed)

Dear Geoff,
Thank you very much for the advice. I tried it but no luck. I've given
up on that option and will stick to creating a macro or two in the
Personal Macro Workbook.
(a) to move and maximize any existing excel file (all currently saved
at 50%)
(b) to do the same for "new" excel files and also increase to 150%.

It is odd, but I can't seem to find any Normal or default template for
Excel in the Maq version of Office X. It must be somewhere - perhaps
hidden? This is easier to find in Windows version. Surely it is
possible to find such a default template and adjust it so all new excel
files open with the same placing, size and zoom settings.

Yours sincerely,
John Bonner

 

[JE McGimpsey]

It is odd, but I can't seem to find any Normal or default template for
Excel in the Maq version of Office X. It must be somewhere - perhaps
hidden? This is easier to find in Windows version. Surely it is
possible to find such a default template and adjust it so all new excel
files open with the same placing, size and zoom settings.

Did you look at the Help topic I listed ('Create a template for
workbooks or worksheets')?

Both Mac and Windows XL have internal default templates.

 

[whitecloud1]

Did you look at the Help topic I listed ('Create a template for
workbooks or worksheets')?

Both Mac and Windows XL have internal default templates.

Yes, I did read it. It offers the following advice and 2 locations for
saving it.
The Startup folder has subfolders foe Word, Excel and another. Do I use
these? They are not mentioned below.

"To Create a workbook template for new workbooks"

1. Create a workbook that contains the sheets, default text (such as
page headers and column and row labels), formulas, macros, styles, and
other formatting you want in new workbooks that you base on the
template.
2. On the File menu, click Save As.
3. On the Format pop-up menu, click Template.
4. Use the column browser to locate and select the folder you want to
store the template in.
To create the default workbook template, select either the Excel
folder in the Microsoft Office X/Office/Startup folder or the alternate
startup folder.

To create a custom workbook template, select the My Templates folder in
your Microsoft Office X/Templates folder.
5. In the Save As box, type Workbook to create a default workbook
template.
To create a custom template, type any valid file name.
-------
I've tried this for default and new files still open small (at 100%)
and need to be adjusted manually to suit. Have given up but perhaps
I've done something foolish?
JB
What spesific name should I give the default excel template? Does it
need a suffix? e.g. .xlt?

 

[JE McGimpsey]

To create a custom workbook template, select the My Templates folder in
your Microsoft Office X/Templates folder.
5. In the Save As box, type Workbook to create a default workbook
template.
To create a custom template, type any valid file name.
-------
I've tried this for default and new files still open small (at 100%)
and need to be adjusted manually to suit. Have given up but perhaps
I've done something foolish?
JB
What spesific name should I give the default excel template? Does it
need a suffix? e.g. .xlt?

Yes, as step 5 says, the name should be "Workbook" (no quotes, no
extensions/suffixes).

 

[whitecloud1]

Yes, as step 5 says, the name should be "Workbook" (no quotes, no
extensions/suffixes).

OK. I'm nearly there. I removed other attempts at default (various
names) and just saved a 150% view empty document as Workbook. All new
workbooks now open at 150%, which is an improvement.

I haven't figured if it is now possible to save the location of the
working window.
I have 2 toolbars open by default and both are set and joined to the
top of the window.

I still have to resort to using a macro to move the window (recorded in
"all new files"). It's a pity I can't do a script or visual basic to
get the macro to operate on opening any document. I'm sure there is a
way. I have QuicKeys and know its possible but should it be necessary
to do this?


Best wishes,
JB

 

[JE McGimpsey]

It's a pity I can't do a script or visual basic to
get the macro to operate on opening any document. I'm sure there is a
way. I have QuicKeys and know its possible but should it be necessary
to do this?

My first reply (on 8/19) illustrates how to run a macro on opening any
document.

 

[whitecloud1]

OK. I'm nearly there. I removed other attempts at default (various
names) and just saved a 150% view empty document as Workbook. All new
workbooks now open at 150%, which is an improvement.

I haven't figured if it is now possible to save the location of the
working window.
I have 2 toolbars open by default and both are set and joined to the
top of the window.

I still have to resort to using a macro to move the window (recorded in
"all new files"). It's a pity I can't do a script or visual basic to
get the macro to operate on opening any document. I'm sure there is a
way. I have QuicKeys and know its possible but should it be necessary
to do this?


Best wishes,
JB

Thanks again. I mentioned I had a problem when I followed your
instructions:
On opening a new file I get the following message:

Compile error: User-defined type not defined.

In the "This Workbook" (Below sheet 1 in Microsoft Excel Objects) the
followin is highlighted in blue: New WindowHandler
If I press Ok, then the first line is highlighted in yellow with an
arrow pointing to it:
"Private Sub Workbook_Open()"

Have I misinterpreted your instructions somewhere?
How do I name the created file MyWindowHandler or whatever you said?
Is it done by the visual basic code anyway?

JB.

 

[JE McGimpsey]

Thanks again. I mentioned I had a problem when I followed your
instructions:
On opening a new file I get the following message:

Compile error: User-defined type not defined.

In the "This Workbook" (Below sheet 1 in Microsoft Excel Objects) the
followin is highlighted in blue: New WindowHandler
If I press Ok, then the first line is highlighted in yellow with an
arrow pointing to it:
"Private Sub Workbook_Open()"

Have I misinterpreted your instructions somewhere?
How do I name the created file MyWindowHandler or whatever you said?
Is it done by the visual basic code anyway?

Yes, you misinterpreted my instructions. WindowHandler is not a file
name, it's the name you assign to a Class module (using the property
browser in the Visual Basic Editor). Class modules (which you can
generate using Insert/Class Module) are different from regular code
modules in that they define a class object which can then be used to
create additional objects.

By creating an object and assigning it to the clsMyWindow, using

Private Sub Workbook_Open()
Set clsMyWindow = New WindowHandler
End Sub

in the ThisWorkbook module of your Personal Macro Workbook (or, as I do
it, my startup add-in), you establish an object that contains a
WithEvents variable (oApp) that responds to application-level events,
such as _WorkbookOpen

I put a slightly modified sample add-in at

ftp://ftp.mcgimpsey.com/excel/whitecloud1_demo.xla

Put it in your startup folder. It's unprotected so you can set
breakpoints and see how it works.

 

[whitecloud1]

Yes, you misinterpreted my instructions. WindowHandler is not a file
name, it's the name you assign to a Class module (using the property
browser in the Visual Basic Editor). Class modules (which you can
generate using Insert/Class Module) are different from regular code
modules in that they define a class object which can then be used to
create additional objects.

By creating an object and assigning it to the clsMyWindow, using

Private Sub Workbook_Open()
Set clsMyWindow = New WindowHandler
End Sub

in the ThisWorkbook module of your Personal Macro Workbook (or, as I do
it, my startup add-in), you establish an object that contains a
WithEvents variable (oApp) that responds to application-level events,
such as _WorkbookOpen

I put a slightly modified sample add-in at

ftp://ftp.mcgimpsey.com/excel/whitecloud1_demo.xla

Put it in your startup folder. It's unprotected so you can set
breakpoints and see how it works.

Thanks for the add-in. It only worked when I removed the Personal macro
workbook from the Excel folder of Startup. If I could modify the script
to open at 150% and move it slightly up and to the right it would help.
I had already set my "Workbook" default to open at 150% and this script
was reducing new files after opening.

I tried changing the Class module name from 'Class 1" but it is
impossible to do this in the browser without Excel shutting down as a
result. I don't know how you renamed yours. Perhaps I'll try this in
Windows and maybe transfer it to mac??

At this stage an "automatic setting" is just an intellectual challenge
for me, as I have already set a 3-key keyboard macro to move any opened
excel file (all at 150% now anyway) a little up and to the left.
I think I'll give up on it for now.

Best wishes,
John.

 

[JE McGimpsey]

I tried changing the Class module name from 'Class 1" but it is
impossible to do this in the browser without Excel shutting down as a
result. I don't know how you renamed yours. Perhaps I'll try this in
Windows and maybe transfer it to mac??

This was a known bug when Office 2004 was first released, but subsequent
updates have fixed it. Are you using an updated version of XL? Use
Office Autoupdate if not - there are a number of small fixes.

 

[whitecloud1]

This was a known bug when Office 2004 was first released, but subsequent
updates have fixed it. Are you using an updated version of XL? Use
Office Autoupdate if not - there are a number of small fixes.

Some GOOD NEWS!
I think I figured out what I was doing wrong.

First of all I'm using a version of Office which is earlier than 2004
(Office X for Mac, which was Microsoft's first version). I did download
2 updates from Mactopia but they didn't solve the shutdown problem, So
I had to think again and came up with what I think is the answer:

I discovered that in order to add the VB scripts I had to use the
"UNHIDE" command in Window drop down menu to see the Personal Macro
Workbook. It wasn't enough just to do the editing in a new excel
workbook and just save it to the Personal Macro Workbook.

In order to change the name of the Class Module in browser, I had to
use copy and paste as it stll shutdown if I tried to delete the
previous name before typing another.

Anyway, the code you suggested does work, and the new working windows
are nearer to the top left corner below the 2 toolbars. However I still
need to increase the magnification to 150% and make a slight adjustment
to the window location. and also drag to the bottom right corner to get
full screen,

I added a keyboard macro to do these 3 things but it must be possible
to add or adjust the classs module to achieve this. What do you think?
Thanks for your patience!

John.

 

[whitecloud1]

Some GOOD NEWS!
I think I figured out what I was doing wrong.

First of all I'm using a version of Office which is earlier than 2004
(Office X for Mac, which was Microsoft's first version). I did download
2 updates from Mactopia but they didn't solve the shutdown problem, So
I had to think again and came up with what I think is the answer:

I discovered that in order to add the VB scripts I had to use the
"UNHIDE" command in Window drop down menu to see the Personal Macro
Workbook. It wasn't enough just to do the editing in a new excel
workbook and just save it to the Personal Macro Workbook.

In order to change the name of the Class Module in browser, I had to
use copy and paste as it stll shutdown if I tried to delete the
previous name before typing another.

Anyway, the code you suggested does work, and the new working windows
are nearer to the top left corner below the 2 toolbars. However I still
need to increase the magnification to 150% and make a slight adjustment
to the window location. and also drag to the bottom right corner to get
full screen,

I added a keyboard macro to do these 3 things but it must be possible
to add or adjust the classs module to achieve this. What do you think?
Thanks for your patience!

John.

Message Part 2:
I think I've cracked it.

Having added a macro to make the 3 adjustments, I examined the
dimensions created in that macro for desired window setting and made
just 5 numerical changes for Top, Left, Width, Height and Zoom using
your script and Eureka! It's working without the need for the keyboard
macro to be typed.

Many thanks and best wishes,
John

 

[whitecloud1]

Message Part 2:
I think I've cracked it.

Having added a macro to make the 3 adjustments, I examined the
dimensions created in that macro for desired window setting and made
just 5 numerical changes for Top, Left, Width, Height and Zoom using
your script and Eureka! It's working without the need for the keyboard
macro to be typed.

Many thanks and best wishes,
John

Now to do the same for Word. How do I modify the VB script(s) to do the
same in Word?
Best wishes,
JB