View Single Post
 
Old 07-03-2023, 12:20 AM
StuW StuW is offline Windows 10 Office 2010
Novice
 
Join Date: Jul 2023
Posts: 1
StuW is on a distinguished road
Default RE: Password protected XLSX won't open maximized

Old thread now, but here is how I fixed this.
Note that this is not elegant! And purely based on my observations, not rigorous analysis.

Background:
There appears to be a bug in Excel 2010 (and likely later versions) when opening password protected files. Something about the on-open password popup box seems to result in Excel defaulting to a non-maximised size, regardless of whether you configure Run=Maximised in the Excel shortcut. Note that if Excel is already open and maximised prior to opening the password-ed file, then it stays maximised ok.

Solution.
Note: Please google concepts below (eg 'Personal.xlsb') if you don't know how they work.

I have seen posts stating the solution is to add VBA code to Personal.xlsb, executed when any workbook is opened. This code triggers off the 'Workbook_Open' event to force the window to maximised. Unfortunately I found this did not work for password protected files. The 'Workbook_Open' event appears to occur BEFORE the worksheet password popup, and it looks like the popup is somehow overriding any prior attempt to maximise the workbook.

After a little playing around, I found a workaround that works for me. The VBA 'Application.OnTime' function can be added to defer the above maximisation for a second, which seems to be enough to place it AFTER whatever the password popup box is doing. As a result Excel is maximised immediately after a password is entered. Yes, this doesnt 100% make sense, but worked when nothing else would!

Within Personal.xlsb:
1) Add the following VBA under "MicrosoftExcelObjects / ThisWorkbook"

Private Sub Workbook_Open()
Application.OnTime (Now() + TimeValue("00:00:01")), "MaximiseExcel"
'1 second delay seems to be enough. Try a little more if this doesnt work reliably.
End Sub

2) Create a new module under "Modules" with the following:

Sub MaximiseExcel()
Application.WindowState = xlMaximized
End Sub
Reply With Quote