CODEWITHSUNDEEP
vbaexceluserform
Chris McCulloch
Chris McCulloch

Reputation: 101

Excel UserForm displays at the wrong size

I'm having a very strange issue with Excel UserForms; when I display a form by clicking a command button in my worksheet, the size of the form warps significantly, making it essentially unusable. The below images illustrate this.

Form before size warping (intended size) Form before size warping (intended size)

Form after size warping Form after size warping

I work with a multiple screen setup, and this sizing issue only occurred at first when just using my laptop without the additional displays; however, it has now started occurring even with my usual setup. Furthermore, in some cases, the warping compounds with each subsequent form initialization. I've included example images of this below.

Form before warping (intended size) Form before warping (intended size)

Form after warping (first iteration) Form after warping (first iteration)

Compounded warping (second iteration) Compounded warping (second iteration)

Further compounded warping (third iteration) Further compounded warping (third iteration)

I've tried setting the height and width properties manually, both through the properties window and through the Userform_Initialize() event, but still end up with strange results. In the properties window, the values I enter for height and width are automatically changed after I enter them, usually resulting in undesirable aspect ratios, and a similar thing happens when I try to resize with VBA. I don't think the code is the issue, as I'm simply setting the .Height and .Width properties so there isn't much room for error.

I've done relatively extensive research and haven't been able to find any information about this issue. I'm using Excel 2016; any help would be appreciated!

Upvotes: 9

Views: 38450

Answers (5)

sijpie
sijpie

Reputation: 21

Another thing to check is: Sometimes (not sure why, but to do with screen resolutions & magnification settings) you may notice that in the VBA editor the forms look miniscule. When dragging a corner to change the size, the contents of the userform instantly change size. Continue to resize the form to show the contents as required. From here on the form should show on the screen as intended.

Upvotes: 0

Clark Davies
Clark Davies

Reputation: 41

Other users may not know to set the compatibility mode. If the spreadsheet will be shared with multiple users, use this code to set the correct form size every time it is opened.

Private Sub UserForm_Initialize()  
'  
' Initialize frm  
' (This runs everytime the form is opened)  
' frm is the form name  
'  
    ' Reset the size  
    With frm  
        ' Set the form size  
        Height = {enter desired form height}  
        Width = {enter desired form width}  
    End With  
End Sub

Upvotes: 4

Simon Wilson
Simon Wilson

Reputation: 11

Check your magnification settings in Windows (Ease of Access). It completely threw my forms sizes. Some were large, others small. Font sizes were as your examples. When a form was created, it inherited the Windows magnification.

Upvotes: 1

hymced
hymced

Reputation: 698

I think your issue might be related to mine here.

@Lance Phillips: the option you are referring to (image below found on the web) is missing on my side with Excel 2016 32-bit Windows 10 64-bit. I have no idea why. Even with an extra monitor connected.

enter image description here

Edit: apparently this is a new feature from Office build 9330.1000, see here.

Upvotes: 8

Lance Phillips
Lance Phillips

Reputation: 31

This is a new issue that is occurring in Excel. I have seen it my applications too. In Excel General Options, set to "optimize for compatibility" and the issue should go away.

Upvotes: 3

Related Questions