Userform sizing

smpatty08

Board Regular
Joined
May 16, 2014
Messages
155
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.
Code:
'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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

As an initial step you could test following line:

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

Instead of butOK ... use butCancel ...
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788
You could use Max.

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top