Problem with VBcode in Excel

[thetoppy]

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

 

[Ardus Petus]

Works by me!

HTH
--
AP

"thetoppy" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[thetoppy]

Thank you Ardus for looking at the code.
I have problems when i load multiple workbooks as a workspace
The code runs fine on one workbook but has errors as a workspace.
Is there any way to fix this???

Thank you