Excel Programming

[Christopher.Top]

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

 

[NickHK]

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

 

[thetoppy]

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..

 

[NickHK]

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