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

Maybe...

Application.WindowState = xlMaximized

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

Bill Martin wrote:

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

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
---------------------------------------------
On 2/24/2010 11:01 AM, Dave Peterson wrote:
Maybe...

Application.WindowState = xlMaximized

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

Bill Martin wrote:

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

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


"Bill Martin" a écrit dans le message de news:
...
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
---------------------------------------------
On 2/24/2010 11:01 AM, Dave Peterson wrote:
Maybe...

Application.WindowState = xlMaximized

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

Bill Martin wrote:

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