Excel user form and windows size

[sjha]

Hello - I have a user who uses rather larger windows settings for view. And, I have this user form with lots of form bases as well as conrol based buttons and when my user opens up the excel file all the buttons seems to shift. I also have a tooltip box and text within that tooltip are also shifting for him. We all are using the same version of MS Excel 2002-2003. Only one user is having this issue and he happens to be my boss. Any idea? I appreciate your help.

 

[sjha]

Any suggestions?

 

[micro-blades]

See if this works for you:

Get your screen width...maximize the excel workbook and type the following in the immediate window in the VBA editor and press enter:

msgbox application.width



Then use the code below to resize the userform and move/resize all controls to fit different screen resolutions.

Change "ActWidth = 966" to the screen width you recieved above.

Private Sub UserForm_Activate()
Dim CurWidth
Dim c As Control
Dim iMult As Double
Dim ActWidth As Integer
Application.WindowState = xlMaximized
[COLOR="red"]ActWidth = 966[/COLOR]
CurWidth = Application.Width
If ActWidth <= CurWidth Then Exit Sub
iMult = CurWidth / ActWidth
With Me 'change userform size
    .Width = .Width * iMult
    .Height = .Height * iMult
    For Each c In .Controls 'change size and location of all the controls
        c.Width = c.Width * iMult
        c.Height = c.Height * iMult
        c.Top = c.Top * iMult
        c.Left = c.Left * iMult
    Next c
End With
End Sub