VBA Express : Excel - Restore default Microsoft size of Workbook.

Excel

Restore default Microsoft size of Workbook.

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

When the code has been run, the application and workbook are both re-sized to a size that approximates the default Microsoft setting that's applied when Office has been freshly installed.

Discussion:

Often, when downloading or unzipping a book that's been saved on a PC with a higher screen resolution you may find it impossible to reach the sheet tabs. If you maximize both the application and window you can reach them but you're then stuck with always viewing it this way. The other usual alternative is to change your screen resolution temporarily, manually re-size everything, then restore your preferred screen resolution (a real pain). With this alternative, you just keep a code module on your desktop and when faced with this problem you simply open the VBE window, import the module and run the code

Code:

instructions for use

			Option Explicit 
Declare Function GetSystemMetrics32 Lib "User32" _ 
Alias "GetSystemMetrics" (ByVal nIndex&) As Long 
 
Sub MakeItUsable() 
    Dim ScrWidth&, ScrHeight& 
    ScrWidth = GetSystemMetrics32(0) 
    ScrHeight = GetSystemMetrics32(1) 
    With Application 
        .WindowState = xlNormal 
        .Width = ScrWidth * 2 / 3 
        .Height = ScrHeight * 2 / 3 
        With .ActiveWindow 
            .WindowState = xlNormal 
            .Width = Application.UsableWidth - 50 
            .Height = Application.UsableHeight - 50 
        End With 
    End With 
    ActiveWorkbook.Save 
End Sub

How to use:

In Excel, press Alt + F11
Go to Insert/Module
Paste the code in the module
Press Alt + Q
Press Alt + F8
Select the macro MakeItUsable/Run (The code also saves)

Test the code:

Download the attachment and extract the contents to the desktop
(The attachment has a workbook open event that should simulate the problem)
Open the attachment, Press Alt + F8 and select the macro MakeItUsable/Run.
The attachment also places the code module "MakeBookUsable" on your desktop
When confronted with this problem in future - do the following:
Press Alt + F11
In the Project Explorer window, right-click and select Import File
Select MakeBookUsable/Open
Press Alt + F8
Select the macro MakeItUsable/Run

Sample File:

Unusablebook.zip 7.78KB

Approved by mdmackillop

This entry has been viewed 125 times.