CODEWITHSUNDEEP
excelvbalistboxuserform
Andreas Hanisch
Andreas Hanisch

Reputation: 21

No effect of .width property in a Excel VBA Userform

I wanted to develop a UserForm that displays multiple columns in a nice and clear pattern.

Overall this also worked out, but no matter what I tried I can not set the Width or the .Height of the ListBox to a different value. With regards to the .idth I always have to use the horizontal scrollbar. This is cumbersome.

How can I set the Width to a certain value and how can I delete the scrollbars. Maybe this does the trick? I searched the web numerous times and obviously in all other examples I have seen the .Width works fine.

Option Explicit

Private Sub UserForm_Initialize()
Dim y()

ReDim y(1 To 3, 1 To 2)
y(1, 1) = "Fuselage"
y(1, 2) = "Painting"
y(2, 1) = "Engines"
y(2, 2) = "Fuel Efficiency"
y(3, 1) = "Landing Gear"
y(3, 2) = "Brake Stress Test"

With Me.Controls.Add("Forms.ListBox.1", "Aviation", True)
.ColumnCount = 2
.List = y
.Width = 500        'No effect
.Height = 600       'No effect
'How to get rid of the scrollbars ?
End With

End Sub

Upvotes: 1

Views: 390

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25693

It appears the order in which the properties are set affects how the listbox displays. The following worked for me:

  1. Position of the control on the form
  2. Dimensions of the control
  3. Number of columns
  4. Column widths
  5. Fill the list

Which makes a certain amount of sense, when one thinks about it... It would be different if VBA, like some more recent programming languages, would let you build the object before inserting it. But VBA creates the object on the form from the minute the Add method is executed and is using defaults if nothing is specified. So once the list has been created apparently the dimensions can no longer be changed. (Just my guess.)

Private Sub UserForm_Initialize()
    Dim y()
    Dim lb As ListBox

    ReDim y(1 To 3, 1 To 2)
    y(1, 1) = "Fuselage"
    y(1, 2) = "Painting"
    y(2, 1) = "Engines"
    y(2, 2) = "Fuel Efficiency"
    y(3, 1) = "Landing Gear"
    y(3, 2) = "Brake Stress Test"

    Set lb = Me.Controls.Add("Forms.ListBox.1", "Aviation", True)
    With lb
        .Top = 80
        .Left = 30
        .width = 200        'No effect
        .height = 100       'No effect
        .ColumnCount = 2
        .ColumnWidths = "100;100"
        .List = y
    End With
End Sub

Upvotes: 1

Related Questions