How to prevent Excel UserForm from shrinking/expanding autonomously?

user_11610720 0
Viewed 9368

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
userform vba excel
5 Answers

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!.

user_113639530

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.

user_111287190

Don't make the form maximized. "Maximized" means you don't know at design-time what the size of the form will be, because you're making that depend on what monitor size it's being displayed in.

Larger monitor = larger maximized form, that's by design.

If you don't want the form to resize automatically when it's displayed in a different-size monitor, then don't maximize it.

Alternatively, handle the form's Resize event, and programmatically move each control where it needs to be relative to the bottom-right edge. Note, that's tricky and extremely annoying code to write, especially if the form is any kind of complicated. Much simpler to just not have a maximized form.

user_11885130

this operation can also be done with a loop. I used the button to increase and decrease the height of userform. I created a loop and assigned it to the button.At the same time there was a nice animation. You set a height value, when the button is pressed, the userform becomes longer if the height is less than this value, and the userform becomes shorter if it is larger. Codes:

Private Sub CommandButton4_Click()
Dim X, d, yuk, mak As Integer
For X = 1 To 100
DoEvents
If e = 0 Then
d = d + 10
yuk = 242
mak = 342
Else
d = d - 10
yuk = 345
mak = 245
End If
UserForm2.Height = yuk + d
If UserForm2.Height >= mak And e = 0 Then GoTo 10
If UserForm2.Height <= mak And e = 1 Then GoTo 20
Next
10 CommandButton4.Caption = "<"
e = 1
ListBox1.ListIndex = 0
ScrollBar1_Change
Exit Sub
20 CommandButton4.Caption = ">"
e = 0
ListBox1.ListIndex = 0
End Sub

It's video :https://www.youtube.com/watch?v=1LCxgQGy9tU

Source and Example file here

user_65657860

I had a similar problem. The only thing that worked for me was going into the Advanced Options of Excel and checking the box, "Disable hardware graphics acceleration." I wasted several hours trying to find this. I hope this helps someone else!

user_127561040
Related