I believe this can be used in place of all that complex code. This won't
likely help the workspace related selection problem though.

Assumed is that A1:M32 is the appropriate range such that if the zoom is
adjusted so that this range fits the screen on "Sheet1" then the zoom is
correct for all worksheets in the particular workbook. Change the range
reference and ws name to suit:-

Sub FitToScreen()
Dim r As Range
Dim ws As Worksheet
Dim z As Integer

Application.ScreenUpdating = False
Set ws = Sheets("Sheet1")
ws.Activate
Set r = Selection 'record existing selection
ws.Range("A1:M32").Select
With ActiveWindow
.Zoom = True
z = .Zoom
r.Select 'reselect prior selection
For Each ws In ThisWorkbook.Worksheets
ws.Activate
.Zoom = z
Next
End With
Application.ScreenUpdating = True
End Sub

Regards,
Greg


"thetoppy" wrote:


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