software

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 quot;advapi32.dllquot; _
Alias quot;GetUserNameAquot; (ByVal lpBuffer As String, nsize As Long) As
Long
Declare Function GetSystemMetrics32 Lib quot;user32quot; Alias
quot;GetSystemMetricsquot; _
(ByVal nIndex As Long) As Long

Function DisplayVideoResolution() As String
DisplayVideoResolution = GetSystemMetrics32(0) amp; quot; x quot; amp; _
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 lt;gt; 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = quot;quot;
End If
End Function

Sub auto_open()
Dim strResolution As String
Dim zoomnumber As Integer
Dim sh As Worksheet
strResolution = DisplayVideoResolution
If strResolution = quot;1152 x 864quot; And fOSUserName = quot;XXXquot; Then
zoomnumber = 100
ElseIf strResolution = quot;1152 x 864quot; Then
zoomnumber = 95
ElseIf strResolution = quot;1024 x 768quot; And fOSUserName = quot;YYYquot; Then
zoomnumber = 85
ElseIf strResolution = quot;1024 x 768quot; And fOSUserName = quot;ZZZquot; Then
zoomnumber = 88
ElseIf strResolution = quot;640 x 480quot; 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: www.excelforum.com/member.php...oamp;userid=33436
View this thread: www.excelforum.com/showthread...hreadid=532525Works by me!

HTH
--
AP

quot;thetoppyquot; gt; a écrit
dans le message de
...
gt;
gt; I have an excel application that is used by different machines within
gt; my business. It comprises of three different wordbooks that I am
gt; loading as a workspace.
gt; Some of the computers have different screen resolution settings and
gt; some have the same resolutions but different size displays.
gt;
gt; I have tried to create a module that looks at the screen resolution
gt; and
gt; then the user name to determine the correct zoom size for each
gt; worksheet.
gt;
gt; The code I have works fine when used alone but when I copy the module
gt; to each workbook and try to load the workspace I get a run time 1004
gt; error at:
gt;
gt; Sh.select
gt;
gt; 'method 'select of object '_ worksheet failed '
gt;
gt; This is a copy of my code::
gt;
gt; Private Declare Function apiGetUserName Lib quot;advapi32.dllquot; _
gt; Alias quot;GetUserNameAquot; (ByVal lpBuffer As String, nsize As Long) As
gt; Long
gt; Declare Function GetSystemMetrics32 Lib quot;user32quot; Alias
gt; quot;GetSystemMetricsquot; _
gt; (ByVal nIndex As Long) As Long
gt;
gt; Function DisplayVideoResolution() As String
gt; DisplayVideoResolution = GetSystemMetrics32(0) amp; quot; x quot; amp; _
gt; GetSystemMetrics32(1)
gt; End Function
gt; Function fOSUserName() As String
gt; Dim lngLen As Long, lngX As Long
gt; Dim strUserName As String
gt; strUserName = String$(254, 0)
gt; lngLen = 255
gt; lngX = apiGetUserName(strUserName, lngLen)
gt; If lngX lt;gt; 0 Then
gt; fOSUserName = Left$(strUserName, lngLen - 1)
gt; Else
gt; fOSUserName = quot;quot;
gt; End If
gt; End Function
gt;
gt; Sub auto_open()
gt; Dim strResolution As String
gt; Dim zoomnumber As Integer
gt; Dim sh As Worksheet
gt; strResolution = DisplayVideoResolution
gt; If strResolution = quot;1152 x 864quot; And fOSUserName = quot;XXXquot; Then
gt; zoomnumber = 100
gt; ElseIf strResolution = quot;1152 x 864quot; Then
gt; zoomnumber = 95
gt; ElseIf strResolution = quot;1024 x 768quot; And fOSUserName = quot;YYYquot; Then
gt; zoomnumber = 85
gt; ElseIf strResolution = quot;1024 x 768quot; And fOSUserName = quot;ZZZquot; Then
gt; zoomnumber = 88
gt; ElseIf strResolution = quot;640 x 480quot; Then
gt; zoomnumber = 50
gt; End If
gt; Application.ScreenUpdating = False
gt; For Each sh In ThisWorkbook.Worksheets
gt; sh.Select
gt; ActiveWindow.Zoom = zoomnumber
gt; Next
gt; ThisWorkbook.Worksheets(1).Select
gt; Application.ScreenUpdating = True
gt;
gt; End Sub
gt;
gt; I need to know what is wrong with the code. Can anyone please help.
gt; Thank you
gt;
gt;
gt; --
gt; thetoppy
gt; ------------------------------------------------------------------------
gt; thetoppy's Profile:
www.excelforum.com/member.php...oamp;userid=33436
gt; View this thread: www.excelforum.com/showthread...hreadid=532525
gt;

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--
thetoppy
------------------------------------------------------------------------
thetoppy's Profile: www.excelforum.com/member.php...oamp;userid=33436
View this thread: www.excelforum.com/showthread...hreadid=532525