UserForm not rendered correctly visually

[Rdhostetler]

hello
i am using excel (office 365) and i have a order entry system where i collect details from the user via user forms. this system is used on 2 PCs here in the office both running the same version of office 365 but on the 1 PC everything works perfect but on the other some of my option buttons are scrambled and not rendered correctly... (see screenshot) - if i click in the blank areas i can make them show up so in order to use it i have to blindly start clicking till i get the right one... any ideas on what is causing this and how to fix it?
thanks

https://1drv.ms/u/s!AsRCLap1vyTSsXlJp7aRCCL9wVHA

 

[NdNoviceHlp]

Check to see that the screen resolution and DPI settings are the same on both pcs. HTH. Dave

 

[Rdhostetler]

hello
thanks for the response! i started digging into and comparing display settings between the 2 computers and what i ended up discovering is that the pc that shows a distorted user form is scaled at 125% instead of 100% and the user thinks he really needs to keep that scale if at all possible. but whenever i change the scale from 125 to 100 the user form renders correctly but my command buttons on the sheet quit working and then if i set the scale to 125 the command buttons work but the user form is distorted... i am assuming there is something that i will need to do on the user form properties to make it render correctly on multiple display settings but not sure what... any suggestions? thanks!

 

[NdNoviceHlp]

That's puzzling why the "sheet" command buttons stop working or did U mean the userform command buttons stop working? Either way, changing the screen resolution should have no effect on the operation of a command button? It takes quite a bit of code to have the userform and controls adjust to pcs with different screen resolution and/or dpi settings. I'll check and see if I have posted any relevant code and provide a link if it's available. Dave

 

[Rdhostetler]

No it was the sheet command buttons that quit working... i agree it was very strange! ok understood... i will wait to hear back and i guess we will go from there... just so strange the way it is acting because we have been using this excel order entry system since 2015 with the same mixed display sizes and never caused an issue (office pro plus 2013) but since we upgraded (or downgraded?) to Office 365 it started acting like that on the 1 computer... thanks for your help!

 

[NdNoviceHlp]

Here's the link... https://www.mrexcel.com/forum/excel...rols-windows-display-text-size-setting-2.html I seem to have updated the code somewhat so I'll post the updated stuff. The code uses some API stuff so I assume that it will only work for 32 bit office installations. Note the "'***adjust to suit" areas of the userform code that may need adjustment. Also, the code is based on developing the original userform with 768 x 1024 screen resolution (this may also need adjustment). Anyways, give this a try. Dave
Module code...

Public Declare Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
Public X  As Long
Public Y  As Long

Public Type ControlPositionType
    Left As Single
    Top As Single
    Width As Single
    Height As Single
    FontSize As Single
End Type

Public m_ControlPositions() As ControlPositionType
Public m_FormWid As Double
Public m_FormHgt As Double

' Save the form's and controls' dimensions.
Public Sub SaveSizes(UF As Variant)
Dim i As Integer
Dim ctl As Control
    ' Save the controls' positions and sizes.
    ReDim m_ControlPositions(1 To UF.Controls.Count)
    i = 1
    On Error Resume Next
    For Each ctl In UF.Controls
        With m_ControlPositions(i)
                .Left = ctl.Left
                .Top = ctl.Top
                .Width = ctl.Width
                .Height = ctl.Height
                'no font for spinbtton ie. error
                If InStr(ctl.Name, "SpinButton") = False Then
                .FontSize = ctl.Font.Size
                End If
        End With
        i = i + 1
    Next ctl
If Err.Number <> 0 Then
On Error GoTo 0
End If
    ' Save the form's size.
    m_FormWid = UF.Width
    m_FormHgt = UF.Height
End Sub

Userform code...

' Arrange the controls for the new size.
Private Sub ResizeControls(UF As Variant)
Dim i As Integer, ctl As Control
    ' Get the form's current scale factors.
    x_scale = UF.Width / m_FormWid
    y_scale = UF.Height / m_FormHgt
    ' Position the controls.
    i = 1
    On Error Resume Next
    For Each ctl In Controls
        With m_ControlPositions(i)
                ctl.Left = x_scale * .Left
                ctl.Top = y_scale * .Top
                ctl.Width = x_scale * .Width
                ctl.Height = y_scale * .Height
                'no font for spinbtton ie. error
                If InStr(ctl.Name, "SpinButton") = False Then
                If InStr(ctl.Name, "ListBox") Then
                ctl.Font.Size = WorksheetFunction.RoundDown(y_scale * .FontSize, 0)
                Else
                ctl.Font.Size = y_scale * .FontSize
                End If
                End If
        End With
        i = i + 1
    Next ctl
If Err.Number <> 0 Then
On Error GoTo 0
End If
End Sub


Private Sub UserForm_Initialize()
Dim Wtemp As Double, HTemp As Double
Call SaveSizes(UserForm1) 'adjust to userform name
X = GetSystemMetrics(SM_CXSCREEN)
Y = GetSystemMetrics(SM_CYSCREEN)
Wtemp = (X - 1024) / 1024
Wtemp = 0.8 - Wtemp / 2 * 0.8 '***adjust .8 to suit
HTemp = (Y - 768) / 768
HTemp = 0.9 - HTemp / 2 * 0.9 '***adjust .9 to suit
Me.Width = Application.UsableWidth * Wtemp
Me.Height = Application.UsableHeight * HTemp
End Sub

Private Sub UserForm_Resize()
Call ResizeControls(UserForm1) '***adjust Userform name to suit
End Sub

 

[Rdhostetler]

ok thanks for that... we do have 32 bit versions so that should not be a problem... i will try to get this implemented over the weekend and we will see how it goes... thanks!

 

[Rdhostetler]

HELLO
thank you very much for this code!! it worked perfectly and allowed my user forms to accommodate multiple display resolutions without distortion!

thanks again!!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave