Resize Excel Window

[Bill Martin]

Using Excel 2003, is there a way to resize the Excel window from within VBA? I
don't mean the window for a particular workbook like:

With ActiveWindow
.Width = Application.UsableWidth
End With

I'm referring to the entire Excel window that the OS opens when you invoke Excel.

Thanks.

Bill

 

[Dave Peterson]

Maybe...

Application.WindowState = xlMaximized

There are a couple other options, too. Check VBA's help for more info.

 

[Bill Martin]

I did look at the VBA help Dave. All I find there is the code I mentioned which
only acts on the sub window and the Application.WindowState you mentioned. The
trouble with that is that it will minimize/maximize the window but it won't let
me tell it to make it some arbitrary size in between.

I suddenly had a "Duh..." moment though while playing with your response. I
tried simply recording a macro while resizing the window manually. Turns out
the following code does what I want:

Application.Left = -390
Application.Top = 87.4
Application.Width = 390
Application.Height = 528.6

The "Left" is negative simply because I'm positioning the window on a second
monitor.

Thanks.

Bill
---------------------------------------------

 

[Mishell]

First you must find the Screen Width and Height :

'<< CODE FOR THE STANDARD MODULE >>

Option Explicit
Public ScrWidth&, ScrHeight&
Declare Function GetSystemMetrics32 Lib "User32" _
Alias "GetSystemMetrics" (ByVal nIndex&) As Long

Private Sub MonitorInfo()
ScrWidth = GetSystemMetrics32(0) '< in pixels
ScrHeight = GetSystemMetrics32(1)
End Sub

Then change the Top, Left, Width and Height properties.


With Application

.WindowState = xlNormal

.Top = 1
.Left = 1

.Width = 400 * ScrWidth / 800

.Height = 300 * ScrHeight / 600


End With

Mishell