HELP - Adjusting Worksheet Viewable Size to fit Smaller Monitors

[lnagel]

On my user's screen I want to show the (A1:V37) range of cells in Worksheet1

The Width of (A1:V37) is 977 pixels
The Height of (A1:V37) is 642 pixels

If the users monitor size => 977X642 then everything works perfect - Scaling is 100% - (A1:V37) is centered on screen and offsets (Top:Left) are exactly what they need to be

The issue comes in when trying to scale these parameters to fit to a monitor size that is not Wide enough (977) or High enough (642)

Been struggling with this piece of code for a couple of days now - Any assistance would be greatly appreciated

Sub Window_Mid()
Dim shW#, maxW#, shH#, maxH#
With Application
    .WindowState = xlMaximized
     maxW = Application.Width 'Returns Monitor Width
     maxH = Application.Height 'Returns Monitor Height
     .WindowState = xlNormal
If maxW >= 977 And maxH >= 642 Then 'If Screen is larger than 977 x 642 then life is good
     Application.Width = 977
     shW = Application.Width
     Application.Height = 642
     shH = Application.Height
Else
     ' Else set the width of Worksheet 1 to 945 x 431
     ' This is hard code for testing on my Laptop Screen - I know this fits
     ' if I can get this part working then I can adapt to other screen width
     Application.Width = 945 '
     shW = Application.Width
     Application.Height = 431
     shH = Application.Height
End If
    .Width = Application.Width
    .Height = Application.Height
    .Top = (maxH - shH) / 2
    .Left = (maxW - shW) / 2
    ' Show me the calculation for offset from Top and offset from Left
    ' Offset works perfect when monitor is larger than desired view
    MsgBox "Offset for Top Margin = " & .Top & " : " & maxH & " - " & shH & " / 2" & vbCr & vbCr & _
    "Offset for Left Margin = " & .Left & " : " & maxW & " - " & shW & " / 2"
End With
' These are the cells that I want to see in the worksheet - and only these
Sheet1.Range("A1:V37").Select
' My attempt at scaling the worksheet screen size to fit monitor
' Works with large monitor - not with small monitor
ActiveWindow.Zoom = True
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

 

[footoo]

Try this :

Sub Window_Mid()
Dim shW#, maxW#, shH#, maxH#
With Application
    .WindowState = xlMaximized
     maxW = .Width 'Returns Monitor Width
     maxH = .Height 'Returns Monitor Height
     .WindowState = xlNormal
    If maxW >= 977 And maxH >= 642 Then
         shW = 977
         shH = 642
    Else
         shW = maxW * 0.98
         shH = maxH * 0.98
    End If
    .Width = shW
    .Height = shH
    .Top = (maxH - shH) / 2
    .Left = (maxW - shW) / 2
    MsgBox "Offset for Top Margin = " & .Top & " : " & maxH & " - " & shH & " / 2" & vbCr & vbCr & _
    "Offset for Left Margin = " & .Left & " : " & maxW & " - " & shW & " / 2"
End With
Range("A1:V37").Select
ActiveWindow.Zoom = True
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

For the Zoom bit of the code, "you cannot zoom in on an arbitrary range. The Zoom area must fill the entire screen. Therefore, either the number of rows or the number of columns must be adjusted so that the zoomed area will fill the entire screen."
Have a look here : http://www.cpearson.com/excel/zoom.htm

 

[lnagel]

Thanks Footoo - Pointed me in the right direction -

Kept having problems with the vba math calculation (shW = maxW * .9 & shH = maxH * .9) not coming out correctly

manually adjusted screensize to 90% and added a sub to tell me what those dimensions actually were

hardcoded those values into Else and "boom" - worked perfect

Couple of thoughts :

1.) Actual values came out for 90% screen at 884.25 X 586.5 - which I'm assuming will disqualify users with monitor resolution of 800 X 600 and Smaller than 90% shows cell Text Strings bleeding over into neighboring cells (font size must not scale) -so not sure how I'm going to handle that (maybe not handle - just pop up MSGBox "Get A Real Monitor or Video Card?)

2.) None of this works without the Range(A1:V37) Select statement - after your post I wasn't sure what that statement did - clear to me now

Anyway - Thanks Very Much for the assist