Userforms shrink on successive openings of an Excel file

[mrbaseline]

Hi,

I've run across the weirdest bug ever and wonder if anybody recognizes it and have any input. In an Excel VBA-project several years in the making, after some recent automatic update to my Excel 365 desktop version, all userforms are rendered smaller and smaller on successive launches of the file. This is what one form looks like after a few "generations":

https://www.dropbox.com/s/pmw0i4p5v0kpq64/Capture.PNG?dl=0

I did a quick test with an empty file, and the same weird mechanics also goes for the Userform designer. Below are shown three identical userforms in terms of size attributes, where each new form has been created after closing and reopening the file:

https://www.dropbox.com/s/wnfdfv2sl9bcuqz/Capture2.PNG?dl=0

Any input greatly appreciated!

 

[My Aswer Is This]

Show us all the code you have in your UserForm

 

[mole999]

not sure of your cause, but often adjusting columns / rows will drag a file down with it, it either needs to be set to a corrected size or decoupled from your code

 

[mrbaseline]

Hi,

Thanks for input. I've narrowed it down to a problem with the userform editor. It would seem that if the userform editor window is left open in the VBA editor, Excel somehow screws it up on saving. The workaround is simple (albeit a bit cumbersome): If a form gets corrupted this way, simply resize it. This will make it snap back to its proper rendition. Then make sure to close the userform edit window before saving. This seems to circumvent the bug.

Cheers!

 

[Hillyman]

Hi,

Thanks for input. I've narrowed it down to a problem with the userform editor. It would seem that if the userform editor window is left open in the VBA editor, Excel somehow screws it up on saving. The workaround is simple (albeit a bit cumbersome): If a form gets corrupted this way, simply resize it. This will make it snap back to its proper rendition. Then make sure to close the userform edit window before saving. This seems to circumvent the bug.

Cheers!

I found that in my case the user form shrinks when I have the userform in the VB editor in one of the two big monitors attached to my laptop. The monitors have different resolutions to the laptop screen., and I suspect this is causing the resizing of the form. When I move the VB editor to the laptop, where the resolution is native to itself, then the user form appears correctly with my macro. I'm using Excel 365.

 

[Arie Bos]

Hi Hillyman,

After days and days of work on a 4K monitor when nothing like this happened, suddenly today I got the same problem. I will follow your solution and get back on it, but it is a very strange thing... the VB editor is not te best one in he world, I think, but this seems to me a real program bug,

cheers!

 

[MrGadget2000]

Sadly five years on this issues still exists.
Closing the VBA editor window has zero effect, it's all about the different monitor sizes.

I'd really love Microsoft to fix this bug, or if anyone has found a real workaround I'd love to know it.

 

[Arie Bos]

MrGadget,

I have solved it in a way that may work.

I found a solution in MrExcel (sorry I forgot who the original writer is).

At the end of each form that opens the next one, I have added:

Private Sub btnNEXT3_Click()
    Unload Me
'this code centers the next form
    With frm41_Overview2
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Height = 600
    .Width = 800
    .Show vbModal
    End With
End Sub

This forces the Forms to open centerd on the speadsheet., so if you make this smaller than the form and center it on your screen, it will not show over two screens anymore.

and at the beginning of each following form, I added:

Private Sub UserForm_Initialize()
    With frm40_Overview1
        Height = 600
        Width = 800
    End With
    Application.ScreenUpdating = True

This seems redundat, but all my forms do show up OK so far. SOmetimess in the editor they do not and I have to mqnually reset them, unfortunately. Wish MS had paid more attention to develop the VBA editor.

Cheers,

 

[NdNoviceHlp]

Both screen resolution and DPI (personalization) settings will affect userform size (and all of the controls on the userform). There's some 32 bit code at this link that resolves these issues....

Userform size displays incorrectly

Hi, I have created a tool for many users in my company. The interface is built around a main userform. I am having an issue with only one user where the userform seems to be so zoomed in on her screen that she is not able to access all of the content. I check her resolution and it is the same...

www.mrexcel.com

Seems like I've changed up the ResizeControls sub over the years...

' Arrange the controls for the new size.
Public 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 UF.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
'MsgBox "Resize Controls error"
End If
End Sub

HTH. Dave

 

[Arie Bos]

Hi Dave, Thank you very much for your response. Will try this weekend to see if I can get it to work. Not a great VBA expert though, so I may come back with a questions.
Arie