Excel Programming
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 ' I need to know what is wrong with the code. Can anyone please help. Thank you 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 ' Returns the network login name 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 |
Excel Programming
Christopher,
You should be using the Workbook_Open event in the ThisWorkbook module. Also, you have no "Case Else" so if there is no match, zoomnumber=0, which is probably not what you want. Would it not be easier for you to decide on a suitable initial range that needs to be visible, then zoom to that, irrespective of the computer and/or user e.g. Range("A2:U2").Select ActiveWindow.Zoom = True NickHK wrote in message oups.com... 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 ' I need to know what is wrong with the code. Can anyone please help. Thank you 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 ' Returns the network login name 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 |
Excel Programming
Thank you NickHK for your reply, I can't understand why the module works individually on the work books but when I open the workspace with all three workbooks in it, I get problems. I’m not too strong on my coding but I cannot see a reason for this to happen. Is there some way that one module can work for all three workbooks? Or should I have a separate module for each one. Thank you so much for you assistance.. -- thetoppy ------------------------------------------------------------------------ thetoppy's Profile: http://www.excelforum.com/member.php...o&userid=33436 View this thread: http://www.excelforum.com/showthread...hreadid=532516 |
Excel Programming
Why not just give each user their own Workspace file, with these workbooks
sets however each wishes, position, zoom etc. Then you can dump all the code. NickHK "thetoppy" wrote in message ... Thank you NickHK for your reply, I can't understand why the module works individually on the work books but when I open the workspace with all three workbooks in it, I get problems. I’m not too strong on my coding but I cannot see a reason for this to happen. Is there some way that one module can work for all three workbooks? Or should I have a separate module for each one. Thank you so much for you assistance.. -- thetoppy ------------------------------------------------------------------------ thetoppy's Profile: http://www.excelforum.com/member.php...o&userid=33436 View this thread: http://www.excelforum.com/showthread...hreadid=532516 |
All times are GMT +1. The time now is 10:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com