CODEWITHSUNDEEP
excelvba
Hakon
Hakon

Reputation: 99

My Userform keep shrinking after every use

Almost every time I use my user form, it shrinks, and after a few times it gets too small to see and I have to go back into the forms on my project and drag it until the size is large again. Is this a result of my code, or is there something that I can do to fix this?

Option Explicit

' called on click of "OK" button
Private Sub CommandButton1_Click()
    MyFile = Me.ComboBox1.Value
    Unload Me
End Sub

' called on click of "Cancel" button
Private Sub CommandButton2_Click()
    Stopped = True
    Unload Me
End Sub

' loads the combo box with the names of all available workbooks
Private Sub UserForm_Initialize()
    Dim wkb As Workbook
    With Me.ComboBox1
        For Each wkb In Application.Workbooks
            If wkb.Name <> ActiveWorkbook.Name Then
                .AddItem wkb.Name
            End If
        Next wkb
    End With
End Sub

Upvotes: 1

Views: 1934

Answers (2)

Neil Bates
Neil Bates

Reputation: 21

If you have this issue, you may like to check; https://www.mrexcel.com/board/threads/userforms-shrink-on-successive-openings-of-an-excel-file.1078705/ For me, adding the following to the userform code at least kept the display right.

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

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

I can't think of a reason for this to happen, especially without any code anywhere tweaking the Height and Width of the form, and with the form Unload-ing itself every time it's shown, even if you're Show-ing the form's default instance it should still be initialized with the design-time values.

You could try forcing a size explicitly in that initialize handler:

Private Sub UserForm_Initialize()
    Me.Height = 480
    Me.Width = 600
    InitializeComponents
End Sub

Private Sub InitializeComponents()
    PopulateAvailableInactiveWorkbooks
    '...
End Sub

Private Sub PopulateAvailableInactiveWorkbooks()
    Dim wkb As Workbook
    With Me.ComboBox1
        For Each wkb In Application.Workbooks
            If wkb.Name <> ActiveWorkbook.Name Then
                .AddItem wkb.Name
            End If
        Next wkb
    End With
End Sub

Upvotes: 1

Related Questions