Testers Wanted

[johnske]

A few testers with a screen resolution of OTHER than 800x600 are needed.

I am running a screen resolution of 800x600 on my machine. I want the attached workbook to appear on your machine the same way that it appears on mine.

When opened, drag the top LH corner workbook to the top LH corner of your screen, if it's doing what I want, it should be measured as (approximately) two-thirds the width and height of your screen.

Please let me know...

Regards,
John

[mdmackillop]

Hi John,
I'm getting errors on the Application.Width and .Height lines.
From the help file

Application.Width is read-only and returns the width of the window icon

[johnske]

Hi John,
I'm getting errors on the Application.Width and .Height lines.

Hi Malcolm,

Any ideas why? And wot resolution are you using?

Thanx,
John

[mdmackillop]

1280 x 1084
See my edited post above. I am not permitted to set simple numeric values either, so it's not related to ScrWidth function.

[Jacob Hilderbrand]

Use this instead.

Run ("MonitorInfo")
With Application.ActiveWindow
            .WindowState = xlNormal
            .Width = ScrWidth / 2   '< Width is in points, ScrWidth in pixels
            .Height = ScrHeight / 2
            .Zoom = 100 * ScrWidth / 800
            .Height = Application.UsableHeight
            .Width = Application.UsableWidth
            .Top = 1
            .Left = 1
      End With

When this runs it makes the workbook fill the entire used area. 1024*768.

[johnske]

I don't understand Malcolm, I cant find that in my Help files (Application.Width is read-only and returns the width of the window icon) and If I run this

With Application
.Width = 400
.Height = 300
End With

it changes the height and width of the application (the workbook) to whatever I set for height and width?

And when I run this Jake, it doesn't fill the screen

With Application.ActiveWindow 
.WindowState = xlNormal 
end with

Anyway, surely I could use Maximize to do that, but that's not what I want to do - I want to set a size other than "maximum"...

[Jacob Hilderbrand]

Oh, do you want to change the actual application window? I was thinking something else. The problem is that the window may be Maximized and as such cannot be moved/resized. Try this.

Application.WindowState = xlNormal
    Application.Left = ...
    Application.Top = ...
    Application.Width = ...
    Application.Height = ...

[mdmackillop]

Apologies John, the quote applies to a minimised window (My help screen text is a bit small on this resolution!!!) BTW I'm on Office2003 and WinXP

[johnske]

Oh, do you want to change the actual application window? I was thinking something else. The problem is that the window may be Maximized and as such cannot be moved/resized. Try this.

Application.WindowState = xlNormal
Application.Left = ...
Application.Top = ...
Application.Width = ...
Application.Height = ...

Yes, the actual application window, the book(s) inside the application window are to be treated differently. That seems to me what could be causing the error...I don't use maximize myself and I forget that most ppl do as a matter of course.

OK, thanx, will get onto normalizing the window first before setting anything else and re-post a modified version of this later to try again...

[mdmackillop]

Hi John,
The following seems to work and I've attached views of my Standard Layout and as it appears using this code.
[VBA] With Application
.WindowState = xlNormal
.Width = ScrWidth / 2 '< Width is in points, ScrWidth in pixels
.Height = ScrHeight / 2
.ActiveWindow.Zoom = 100 * ScrWidth / 800
.ActiveWindow.WindowState = xlNormal
.ActiveWindow.Height = Application.UsableHeight
.ActiveWindow.Width = Application.UsableWidth
.ActiveWindow.Top = 1
.ActiveWindow.Left = 1
End With
[/VBA]

[johnske]

Thanx for that Malcolm,

I also found that I need to put ActiveWindow.WindowState = xlNormal also - which I see you've done - (for the case where the ActiveWindow has been maximized as well as the application window).

This gives me something concrete to work on now, I want to now do some more on this to "read" all the window states and sizes on closing, store that info in cells somewhere so that if it's opened on another machine with a different screen resolution all the adjustments are made automatically

Regards,
John

[mdmackillop]

How about storing the info in the document properties

Option Explicit 

Private Sub Workbook_Open()
Dim Tmp As String
Run ("MonitorInfo")
'Add CustomProperty
'AddProp Name, Type, Value
'Type: Number = 1, Boolean = 2, Date = 3, String = 4 or Float = 5
On Error Resume Next
Tmp = ActiveWorkbook.CustomDocumentProperties("Wide")
If Err = 5 Then
Err.Clear
AddProp "Wide", 1, ScrWidth
Else
ActiveWorkbook.CustomDocumentProperties("Wide") = ScrWidth
End If
MsgBox ActiveWorkbook.CustomDocumentProperties("Wide")
End Sub
 
Sub AddProp(PropName As String, PropType As Long, PropVal As Long)
On Error Resume Next
With ActiveWorkbook.CustomDocumentProperties
.Add Name:=PropName, _
LinkToContent:=False, _
Type:=PropType, _
Value:=PropVal
End With
End Sub

[johnske]

Ok, in this version, you should be able to view the book with my screen resolution settings. If you resize it etc. and save, those sizes will be saved now but the view of the book should stay at my screen resolution.

Hopefully there shouldn't be any errors now though...(notice I did say hopefully )

For those that may be "iffy" about trying... Here's the enclosed code.

Option Explicit

Private Sub Workbook_Open()
'I'm using 800x600 resolution, this should
'keep your view of this the same as mine
Run ("MonitorInfo")
With Application
.WindowState = xlNormal
.Left = Range("IV65501")
.Top = Range("IV65502")
.Width = ScrWidth * Range("IV65503") / 800
.Height = ScrHeight * Range("IV65504") / 600
With .ActiveWindow
.WindowState = xlNormal
.Left = Range("IV65505")
.Top = Range("IV65506")
.Width = ScrWidth * Range("IV65507") / 800
.Height = ScrHeight * Range("IV65508") / 600
.Zoom = ScrWidth * Range("IV65509") / 800
End With
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets(1)
Run ("MonitorInfo")
Range("IV65501") = Application.Left
Range("IV65502") = Application.Top
Range("IV65503") = Application.Width
Range("IV65504") = Application.Height
Range("IV65505") = Application.ActiveWindow.Left
Range("IV65506") = Application.ActiveWindow.Top
Range("IV65507") = Application.ActiveWindow.Width
Range("IV65508") = Application.ActiveWindow.Height
Range("IV65509") = Application.ActiveWindow.Zoom
End With
End Sub[/vba][vba]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

EDIT: You musta posted at the same time as me Malcolm, will have a look 2morro, (it's getting late here)

[Jacob Hilderbrand]

Seems to be working for me now. At 1024*768 the app is resized to about 2/3 the width and height of my screen.