• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto zoom on open

vasim

vasim

Member
I have got the following code from

http://excel.tips.net/T002668_Always_Open_at_100_Zoom.html


The code works fine on 2007 but throws an error in 2010 version,any reason or help.

[pre]
Code: 
Declare Function GetSystemMetrics32 Lib "user32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

Public Sub ScreenRes()
Dim lResWidth As Long
Dim lResHeight As Long
Dim sRes As String

lResWidth = GetSystemMetrics32(0)
lResHeight = GetSystemMetrics32(1)
sRes = lResWidth & "x" & lResHeight
Select Case sRes
Case Is = "800x600"
ActiveWindow.Zoom = 75
Case Is = "1024x768"
ActiveWindow.Zoom = 125
Case Else
ActiveWindow.Zoom = 100    'throws error
End Select
End Sub

Private Sub Workbook_Open()
ScreenRes
End Sub
[/pre]
 
Indian


Your code works fine on my Excel 2010 when running at 1920 x 1200 I get 100% Zoom


A few things to check


If you have installed Excel 64 bit the declare line should be

Declare Ptrsafe Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long


Have you put the Private Sub Workbook_Open() in the ThisWorkbook module?


Have you put the Declare and Public Sub ScreenRes() in a Code module?


What values of lResWidth and lResHeight do you get ?
 
Hi Hui,


Its works great on my PC but not on my bosses PC.....


We assumed that its because of version change. How can I know whether he/she has installed 64/32 bit....and yes Workbook_open is in this workbook and declare & Public sub in code module...


The IresWidth & IresHeight on my PC is showing 1366 and 768 respectively.


And yes, Thanks Thanks Thanks Thanks for always being there for help.....
 
Alternatively, I tried the following code in workbook module but same problem - works on my PC but not on my boss....


Private Sub Workbook_Open()

Application.ScreenUpdating = False

Sheet1.Range("A1:Q30").Select 'throws error while opening

ActiveWindow.Zoom = True

[B10].Select

Application.ScreenUpdating = True

End Sub
 
Hi ,


When you say the same procedure throws an error on your boss's PC , what is the error message ? What is the value of sRes when the error message is displayed ?


Does the error message come when the zoom is already 100 % or when it is some other value ?


Narayan
 
Thanks Narayan....for that I would have to wait till he calls me (he's the CEO) thats why....will test and confirm soon
 
Hi Indian,


I don't have Excel 2010 so can't comment on that part but at the bottom of code tip you will see following quote


Quote:

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2668) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Unquote


So while the code may work for 2010 but it has not been tested for 2010 by author.
 
Your code also works fine in my version of Excel 2010. Like others have indicated, if you could take a screen capture of the error - or describe the error in text - that would be helpful toward diagnosing the problem.


Alternatively, you might consider this tutorial:

http://optionexplicitvba.blogspot.com/2011/10/one-size-fits-all.html


...which does not require using windows API and I've found works pretty well across different monitors.
 
Indian


As you have only given us the code without the actual workbook/sheet, I am starting to suspect that there could be some interaction with the workbook/sheet that is at play here


Does the workbook/sheet have any Hidden Columns/Rows on that page ?

Does the workbook/sheet have any protected areas, cells, objects etc on the page ?


Can you post the workbook here or email to me?
 
Error Message that pops up with ScreenRes on 2010,


Run time error 91

Object Variable or with block variable not set...


Hui,


No hidden columns/row (a worksheet hidden only), no protected areas, cells or anything.

Would Email you soon.
 
Thanks Jordan,


But the code provided by still throws the error as


Run time error 1004

Select method of range class failed.


Only on 2010...god knows whats wrong...

The error cursor is when the command is on select statement.
 
Indian


Can you please try this slightly modified code:

[pre]
Code: 
Public Sub ScreenRes()
Dim lResWidth As Long
Dim lResHeight As Long
Dim sRes As String

lResWidth = GetSystemMetrics32(0)
lResHeight = GetSystemMetrics32(1)
sRes = lResWidth & "x" & lResHeight
Select Case sRes
Case "800x600"
ActiveWindow.Zoom = 75
Case "1024x768"
ActiveWindow.Zoom = 125
Case Else
ActiveWindow.Zoom = 100
End Select
End Sub
[/pre]
 
Hi ,


When you say the statement :

[pre]
Code: 
Sheet1.Range("A1:Q30").Select
throws an error while opening the workbook , do you know what sheet is visible on the screen ? Is it Sheet1 or some other sheet ? Is Sheet1 hidden or visible ?


Have you tried by replacing the above statement with a fully qualified one , such as :

[pre][code]ThisWorkbook.Worksheets("Sheet1").Range("A1:Q30").Select
[/pre]
Try working your way down to the range level , by splitting up the above single statement into two separate statements :

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1:Q30").Select[/code][/pre]
and see whether the error now appears on the first statement or the second.


Narayan
 
You may also want to change your initial subroutine

[pre]
Code: 
Private Sub Workbook_Open()
Application.ScreenUpdating = True
ScreenRes
End Sub
[/pre]
 
Can you add this line to the code and let us know what is displayed in the MsgBox?

[pre]
Code: 
Public Sub ScreenRes()
Dim lResWidth As Long
Dim lResHeight As Long
Dim sRes As String

lResWidth = GetSystemMetrics32(0)
lResHeight = GetSystemMetrics32(1)
sRes = lResWidth & "x" & lResHeight
Msgbox sRes
Select Case sRes
Case "800x600"
ActiveWindow.Zoom = 75
Case "1024x768"
ActiveWindow.Zoom = 125
Case Else
ActiveWindow.Zoom = 100
End Select
End Sub
[/pre]
 
Indian


On the Report Worksheet can you change the zoom to anything except 100

In the Immediate window can you type

ActiveWindow.Zoom = 100

and press enter

Let us know what happens
 
Narayan


Indian has sent me the file in question and it works fine on my Excel 2010 install


Indian

If my last 4 comments above don't help

I'm starting to think that an Excel Re-install is in order

Make sure you install VBA as part of it, as well as the Analysis Tool Pack and Analysis Toolpak VBA addins


ps: Nice Dashboard!
 
Before messing with him, would check on other PC in my office. If in case the problem occurs on other PC (which shouldn't as Hui said) then would try all the alternatives provided...


PS: Hui...your two words....I cant explain my happiness...
 
You must log in or register to reply here.
Back
Top