How to prevent Excel UserForm from shrinking/expanding autonomously?

Question

My Excel UserForms contain a variety of objects, including text boxes, combo boxes, radio buttons, etc. The UserForm and the objects on the UserForm shrink and expand when my laptop is on a docking station and the VBA window is open on a larger monitor.

When I access the UserForm editor from the Forms tab in VBA, I can drag the UserForm resize handles and the objects in the UserForm will immediately snap back to their original state, but I want to do this programmatically so that the end user will not deal with shrunken/expanded UserForms.

I have tried resizing the UserForm upon opening (UserForm_Initialize), but it seems as if the shrinking/expanding takes place while the UserForm is not active, meaning that my UserForm resizing only acts to return the UserForm to its shrunken/expanded state and not its original state.

Sub UserForm_Initialize()

 Call ResizeUserform(Me)

End Sub

Sub ResizeUserform(UserForm_Name As Object)

 UserForm_Name.Width = UserForm_Name.Width + 0.001
 UserForm_Name.Width = UserForm_Name.Width - 0.001

 UserForm_Name.Height = UserForm_Name.Height + 0.001
 UserForm_Name.Height = UserForm_Name.Height - 0.001

End Sub

Answer 1

Don't leave your form's dimensions ambiguous or prone to logic circularity (i.e. as a function of itself); set them up before loading/showing.

i.e.:

   'where XX and YY are integer constants:
   With YourFormName
        .width=XX
        .height=YY
        .show
    end with

If you absolutely need to incur in circular statements, do it indirectly by storing your calculated variable in a global/local variable, and then proceed to declare its properties (i.e. YourFormName.width=variable)

Good luck!.

Answer 2

I had a similar issue, ever time the program opened the Login user form would be smaller than the last time. It only did it on my lap top with extra monitors. I finally used

Private Sub UserForm_Initialize()
   With Userform
      .Width = Application.Width * 0.3
      .Height = Application.Height * 0.6    
   End With
End Sub 

in the userforms code and it kind of stopped meaning it no longer showed the user the change but if I open the VBA it had changed size on the Height and Width but since it was only in the VBA and the user didn't have to try and enter a password in a mini box its fine.