Userform sizing

[smpatty08]

I am having some trouble adapting a code I found online to list all the modules for open workbooks. The code works great but if there are a lot of modules then the UserForm box becomes so large I can't see the buttons on the bottom. I want to add a scrollbar to it so the UserForm can stay the same height. Here is the code I have so far.

'in Userform1 code moduleOption Explicit
Public WithEvents aListBox As MSForms.ListBox
Public WithEvents butOK As MSForms.CommandButton
Public WithEvents butCancel As MSForms.CommandButton
Public WithEvents butRemove As MSForms.CommandButton


Dim promptLabel As MSForms.Label


Private Sub aListBox_Click()
    butOK.Enabled = True
    butRemove.Enabled = True
End Sub


Private Sub butCancel_Click()
    Me.Tag = vbNullString
    Unload Me
End Sub


Private Sub butOK_Click()
    With aListBox
        If .ListIndex <> -1 Then
            Call AddLineNumbers(.Value, .text)
        End If
    End With
    butOK.Enabled = False
    butRemove.Enabled = True
    aListBox.SetFocus
End Sub


Private Sub butRemove_Click()
    With aListBox
        If .ListIndex <> -1 Then
            Call RemoveLineNumbers(.Value, .text)
        End If
    End With
    butRemove.Enabled = False
    butOK.Enabled = True
    aListBox.SetFocus
End Sub


Private Sub UserForm_Activate()
    Dim oneWorkbook As Workbook
    Dim oneComponent As VBComponent
    Dim oneCodeModule As CodeModule
    Dim sizeLabel As MSForms.Label
    Dim fontName As String, fontSize As Long
    fontName = "Arial": fontSize = 12


    Set promptLabel = Me.Controls.Add("Forms.Label.1")
    With promptLabel
        With .Font
            .Name = fontName: .Size = fontSize + 2
        End With
        .BorderStyle = fmBorderStyleNone
        .Top = 5
        .Left = 10
        .Width = 590
        .Caption = Me.Tag
        .AutoSize = True
        .WordWrap = True
        .Width = 590
    End With


    Set aListBox = Me.Controls.Add("Forms.ListBox.1")
    With aListBox
        .Top = promptLabel.Top + promptLabel.Height + 10
        .Left = promptLabel.Left
        .Width = 590
        .Height = 100
        .ColumnCount = 2
        .BoundColumn = 1: .TextColumn = 2
        With .Font
            .Name = fontName
            .Size = fontSize
        End With
    End With
    
    Set sizeLabel = Me.Controls.Add("Forms.Label.1")
    With sizeLabel
        With .Font
            .Name = fontName
            .Size = fontSize
        End With
        .AutoSize = True
        .Visible = False
    End With


    For Each oneWorkbook In Application.Workbooks
        If oneWorkbook.Windows(1).Visible Then
            For Each oneComponent In oneWorkbook.VBProject.VBComponents
                If Not ((oneWorkbook.Name = ThisWorkbook.Name And oneComponent.Name = "UserForm1") _
                            Or (oneWorkbook.Name = ThisWorkbook.Name And oneComponent.Name = "Module1")) Then
                    If oneComponent.Type <> vbext_ct_ClassModule Then
                        aListBox.AddItem oneWorkbook.Name
                        aListBox.List(aListBox.ListCount - 1, 1) = oneComponent.Name
                        sizeLabel.Caption = sizeLabel.Caption & vbCr & "X"
                    End If
                End If
            Next oneComponent
        End If
    Next oneWorkbook


    aListBox.Height = sizeLabel.Height
    Me.Controls.Remove sizeLabel.Name


    Set butOK = Me.Controls.Add("Forms.CommandButton.1")
    With butOK
        With .Font
            .Name = fontName
            .Size = fontSize + 2
        End With
        .Default = True
        .AutoSize = True
        .Caption = "Add line labels"
        .AutoSize = False
        .Height = .Height - 4
        .Top = aListBox.Top + aListBox.Height + 16
        .Left = aListBox.Left + aListBox.Width - .Width
    End With


    Set butRemove = Me.Controls.Add("Forms.CommandButton.1")
    With butRemove
        With .Font
            .Name = fontName
            .Size = butOK.Font.Size
        End With
        .Caption = "Remove"
        .Width = butOK.Width
        .Height = butOK.Height
        .Top = butOK.Top
        .Left = butOK.Left - .Width - 20
    End With


    Set butCancel = Me.Controls.Add("Forms.CommandButton.1")
    With butCancel
        With .Font
            .Name = fontName
            .Size = butOK.Font.Size
        End With
        .Caption = "Close"
        .Height = butOK.Height
        .Width = butOK.Width
        .Top = butOK.Top
        .Left = butRemove.Left - .Width - 20
    End With


    With Me
[COLOR=#ff0000]        .ScrollBars = fmScrollBarsVertical[/COLOR]
[COLOR=#ff0000]        .ScrollHeight = .InsideHeight * 2[/COLOR]
[COLOR=#ff0000]        .ScrollWidth = .InsideWidth * 9[/COLOR]
[COLOR=#ff0000]        .ScrollTop = 0[/COLOR]
        .Width = 2 * aListBox.Left + aListBox.Width
        .Height = butOK.Top + 2 * butOK.Height + 10
    End With
    butOK.Enabled = False
    butRemove.Enabled = False
    aListBox.SetFocus
End Sub

The red lines I have added, but the UserForm stills auto sizes to the ListBox height and I can not figure out how to get it to stop. Any ideas?

 

[James006]

Hello,

As an initial step you could test following line:

.Height = butCancel.Top + 2 * butCancel.Height + 10

Instead of butOK ... use butCancel ...

 

[mikerickson]

You could use Max.

With Me
    ' ;;;
    .Width = WorksheetFunction.Max (2 * aListBox.Left + aListBox.Width, 600)
End With

 

[smpatty08]

You could use Max.

With Me
    ' ;;;
    .Width = WorksheetFunction.Max (2 * aListBox.Left + aListBox.Width, 600)
End With

That does work, but then the UserForm does not show the buttons at the bottom.

 

[mikerickson]

Well, if you know that the particular buttons are at the bottom of the UF, you could key on them to find the height.

If you are adding controls at run-time and want to make sure that everything is visible, you could put the added controls in a Frame. The frame size would be adjusted as each control was added and (if it gets too tall) the frame height could be restricted and scroll bars on that frame activated.

Note: when sizing a userform based off of a control's Top and Height, you also need to add the factor (Userform1.Height - Userform1.InsideHeight) to account for the UF's title bar.