CODEWITHSUNDEEP
excelvbalistbox
Ale
Ale

Reputation: 81

Resizing a ListBox using VBA

I have a ListBox in my UserForm, which gets filled with items from a sheet. No multi column. This ListBox together with UserForm should resize automatically depending on how many items are in the ListBox.

When I activate my UserForm it gets resized. The ListBox is not resizing correctly.

If I put a breakpoint just before resizing and then resume activation with F5 or F8 all works as intended. I have done this many times and all worked till now.

Here is what my UserForm looks like initially and the code.

enter image description here

Private Sub UserForm_Activate()
    Dim i As Long   
    Me.ListBox1.Clear
    For i = 2 To shSet.Range(wConst & "2").CurrentRegion.Rows.Count 
        Me.ListBox1.AddItem shSet.Cells(i, wConst).Value
    Next i
    If Me.ListBox1.Height < Me.ListBox1.ListCount * 14 Then
        Me.ListBox1.Height = Me.ListBox1.ListCount * 14
        Me.Height = Me.ListBox1.Height + 40
    End If
End Sub

Here is what it supposed to look like:

enter image description here

Here is what it looks like, when the problem occurs:

enter image description here

Upvotes: 1

Views: 2344

Answers (2)

EvR
EvR

Reputation: 3498

First make your Userform larger, then after that your Listbox

If Me.ListBox1.Height < Me.ListBox1.ListCount * 14 Then
    Me.Height = Me.ListBox1.Height * 14 + 40
    Me.ListBox1.Height = Me.Height - 40

 End If

Upvotes: 1

Variatus
Variatus

Reputation: 14383

Try rephrasing the code. For example,

Dim Hight As Single

Hight = (i - 2) * 14 + 40
If Me.Height <> Hight Then
    With Mw.ListBox1
        If .Height <> (Hight - 40) Then .Height = Hight - 40
    End With
    .Height = Hight
End If

Perhaps VBA doesn't like the ListBox to be bigger than the form at that moment. Try setting its size first. - That sort of thing.

Upvotes: 1

Related Questions