Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Auto Zoom Module
I have an excel application that is used by different machines within my business. It comprises of three different wordbooks that I am loading as a workspace. Some of the computers have different screen resolution settings and some have the same resolutions but different size displays. I have tried to create a module that looks at the screen resolution and then the user name to determine the correct zoom size for each worksheet. The code I have works fine when used alone but when I copy the module to each workbook and try to load the workspace I get a run time 1004 error at: Sh.select 'method 'select of object '_ worksheet failed ' This is a copy of my code:: Private Declare Function apiGetUserName Lib "advapi32.dll" _ Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long Function DisplayVideoResolution() As String DisplayVideoResolution = GetSystemMetrics32(0) & " x " & _ GetSystemMetrics32(1) End Function Function fOSUserName() As String Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX < 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If End Function Sub auto_open() Dim strResolution As String Dim zoomnumber As Integer Dim sh As Worksheet strResolution = DisplayVideoResolution If strResolution = "1152 x 864" And fOSUserName = "XXX" Then zoomnumber = 100 ElseIf strResolution = "1152 x 864" Then zoomnumber = 95 ElseIf strResolution = "1024 x 768" And fOSUserName = "YYY" Then zoomnumber = 85 ElseIf strResolution = "1024 x 768" And fOSUserName = "ZZZ" Then zoomnumber = 88 ElseIf strResolution = "640 x 480" Then zoomnumber = 50 End If Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Select ActiveWindow.Zoom = zoomnumber Next ThisWorkbook.Worksheets(1).Select Application.ScreenUpdating = True End Sub I need to know what is wrong with the code. Can anyone please help. Thank you -- thetoppy ------------------------------------------------------------------------ thetoppy's Profile: http://www.excelforum.com/member.php...o&userid=33436 View this thread: http://www.excelforum.com/showthread...hreadid=532520 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help! Print Preview Zoom is Grayed Out...Doesn't zoom. | Excel Discussion (Misc queries) | |||
Problem with Auto Close module | Excel Discussion (Misc queries) | |||
set excel zoom in 80 | Excel Programming | |||
zoom in Excel | Excel Discussion (Misc queries) | |||
zoom in Excel | Excel Discussion (Misc queries) |