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