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!

 

[MrGadget2000]

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,

The way I call the userform is much the same as you suggested, but I tried adding the second code to the initialisation of the userform and on first run (on an already shrunk form) it worked perfectly!
Thank you so much! This has been a pain in the backside for such a long time!

 

[NdNoviceHlp]

edit: Whoops. Dave

 

[Arie Bos]

Dave,
I am not sure how to use your code.
I have 14 consecutive forms. When I execute them, they appear OK, 800 x 600, and subforms 700 x 500, all centered.
But in the editor, the forms show in all different widths and heights, always much smaller.

So, I was wondering if a macro could be stored in Module 1 to reset to sizes back to 800x600 (or 700x500) with a shortcut, instead of constantly having to reset them manually?

 

[NdNoviceHlp]

Hi Arie. That's what the code does. Follow the code placement instructions at the link. A portion of the code is module code and then each userform requires some code as outlined. Substitute the ResizeControls sub with the updated code that I posted previously. The only change required for each userform is this part...

Private Sub UserForm_Resize()
Call ResizeControls(UserForm1)
End Sub

Change the "Userform1" to the name of the userform. Also, this part of the userform initialize code may need adjustment...

'design form at 1024 x 768 resolution
Wtemp = (X - 1024) / 1024
'adjust .65 width to suit (form fills %65 of screen width)
Wtemp = 0.65 - Wtemp / 2 * 0.65
HTemp = (Y - 768) / 768
'adjust .9 height to suit (form fills %90 of screen height)
HTemp = 0.9 - HTemp / 2 * 0.9

It depends what your screen resolution is at design time and how much of the screen you want the userform to fill. Make adjustment to suit.HTH. Dave

 

[Arie Bos]

Hi Dave,

I am not sure how to use your code and where to place it
I have a file with 14 consecutive forms, all 800x600. When executing, they all show fine. But in the editor, they come up with all kinds of dimensions, but always shrunk.

I was wondering if a code could be placed in a module whith which uone can use a shortcut to restore the forms while in the editor?

 

[NdNoviceHlp]

Hi again Arie. As far as how to use the code and where to place it, please see my previous post. As for restoring a form in the VBE, I have no idea. I do not have any experience with a form OK when shown but messed up in the VBE. The code I was referring to maintains the form as designed in the VBE independent of screen resolution and dpi settings when shown. It doesn't seem like I'll be able to provide U any further assistance. Dave