Opening a workbook window maximized

[John Crosher]

I am trying to ensure that the sheet window in an Excel workbook always opens
maximized and remains protected in that state, so that users cannot resize
it. If the window element of the workbook is protected and saved before
closing the workbook, it always reopens as a restored window, not maximised,
regardless of the window size when it was closed.

That is the case whether the sizing and protection is done manually, or
using a code fragment such as:-
With Workbooks(WBName)
.Unprotect
.Windows(1).WindowState = xlMaximized
.Protect , True, True
.Close True
End with

This behaviour appears to conflict with the Excel Help (e.g.XLXP) for
"Workbook protection (Windows)", which says:-
"Windows element, When selected, prevents users from:
"Changing the size and position of the windows for the workbook when the
workbook is opened."
"Moving, resizing, or closing the windows. However, they can hide and
unhide windows." "

This behaviour seems to have existed since XL97 through XL2003, and there is
no MS KB article which addresses this issue.

Can anyone help, please?

 

[Dave Peterson]

So why not just open the workbook the way you want:

Option Explicit
Sub auto_open()
With ThisWorkbook
.Unprotect
.Windows(1).WindowState = xlMaximized
.Protect , True, True
End With
End Sub

 

[John Crosher]

Dave, thanks for the reply. This workbook is not going to be used by me, but
by other (usually unskilled) users. I have set it up to run fully
automatically as a bespoke program for them to use, and they don't have any
normal XL facilities, which have been hidden. Several worksheets are opened
(and closed) by the program, and have to open maximised for them to be
properly viewed. This works 99% of the time, but just occasionally fails,
without apparent rhyme or reason, and I am trying to find a way to make it
work 100%.

 

[John Crosher]

Dave, further to my last post, I can (and will at the next release) introduce
the code you suggest. However, I was really trying to understand the
situation fully, and why this case doesn't appear to follow MS's intentions
as far as protecting a workbook window shape.

 

[Dave Peterson]

I don't know why your original code doesn't do what you expect. It didn't work
for me either.

But if you're saying the code I suggested didn't work, you could add some more
checks...

Option Explicit
Sub auto_open()
With ThisWorkbook
.Unprotect
.Windows(1).Visible = True
If .Windows(1).WindowState = xlMinimized Then
.Windows(1).WindowState = xlNormal
End If
.Windows(1).WindowState = xlMaximized
.Protect , True, True
End With
End Sub

I couldn't duplicate it in today's testing, but I kind of recall a problem going
from minimized to maximize directly. But again, I didn't have trouble with that
today.

ps. I'm using xl2003 under WinXP Home. (I wouldn't imagine that it would make
a difference--but who knows for sure???)

 

[John Crosher]

Dave, I wasn't suggesting your code doesn't work, and thanks for the extra
code. The question really is why is code necessary at all for this scenario.
If the window element is protected, why doesn't it re-open the same size as
it was closed? Isn't that one of the purposes of the protection? For me it
fails under all levels of XL since 97, on all Windows platforms.

 

[Dave Peterson]

I don't have a guess why it doesn't work.

Dave, I wasn't suggesting your code doesn't work, and thanks for the extra
code. The question really is why is code necessary at all for this scenario.
If the window element is protected, why doesn't it re-open the same size as
it was closed? Isn't that one of the purposes of the protection? For me it
fails under all levels of XL since 97, on all Windows platforms.

 

[John Crosher]

Dave, thanks for your help. Perhaps someone else has an idea, or I shall have
to raise it with Microsoft support.