CODEWITHSUNDEEP
excelvbaexcel-2010
ubercam
ubercam

Reputation: 761

Userform ListBox width changes automatically

I've got a UserForm in an Excel VBA project. This form features, amongst other items, two ListBox objects side by side with a couple buttons in between to move items between the lists.

In the form designer, I've drawn the lists exactly where I want them, and both have identical dimensions.

As both ListBoxes have to be multi-column, after retrieving the data from the database, I use a slightly modified version of JKP's ListBox AutoSize code to get the column widths, work out the column count, and finally populate the list with data.

The VBA code snippet performing this task is

' GetColumnWidths is my modified version of JKP's Autosize code
' vaData is a 2 dimensional variant array containing the data
' LISTBOX_WIDTH is a constant width value (my attempt to stop this behaviour)

With lbxList

    .ColumnWidths = GetColumnWidths(vaData)
    .ColumnCount = UBound(vaData, 2) + 1
    .List = vaData
    .Width = LISTBOX_WIDTH

End With

When the form initially loads, the lists are blank until the user makes a selection from a ComboBox. Until they do that, everything looks fine and exactly as intended. When the user makes the selection, things change.

Apologies for my rubbish redacting, but this contains potentially commercially sensitive info, not to mention it's irrelevant for the purposes of this question.

The form looks like this when it loads initiallly: Initial Load

After the user selects an item from the ComboBox, the lists populate with items. Notice the scroll bars of the right-hand list look slightly stretched and odd. After user selects an item from the ComboBox

After the user clicks an item in the right-hand list, the size suddenly changes! After the user clicks an item in the right-hand ListBox

The only way to get the size back to normal is to move an item across to the left-hand list, then back. Once you do that, they stablise and are fine, until you close and open the form again.

For what it's worth, we're using Excel 2010 32-bit.

I've done a bit of testing and it seems that changing the ColumnWidths property affects the Width property, but setting the Width back to what it was originally after that should in theory solve the problem, but it seems only until you actually click on the ListBox.

Now, for my question... does ANYONE know what the heck is going on here? I've had two people look at this and everyone's scratching their heads! This isn't a showstopper or anything, but it's definitely odd enough. I've searched high and low for anything even remotely related to this and couldn't find a thing, so I thought I'd post it here.

To get around it, I added the MouseDown event to each ListBox and force a Width change in there. It doesn't stop it redrawing narrower, but it does reset the width properly.

Thanks!

Upvotes: 1

Views: 9864

Answers (3)

ThePennyDrops
ThePennyDrops

Reputation: 171

I am just a code rabbit, but my experience is it is a timing issue in VBA.

My form is very simple, just a listbox, yet I could not code stable listbox dimension changes until I stumbled on a post by 'HansV'.

I might have gone overboard but, after the completion of every loop affecting any dimension change, I added a "DoEvents" statement and my code worked like magic.

With Me
  .Height = 99
.. Other changes
End With
DoEvents

Thank you 'HansV' [1]: https://eileenslounge.com/viewtopic.php?t=13053#post_content96707

Upvotes: 2

robertocm
robertocm

Reputation: 132

A counter-intuitive solution that has worked for me:

  • place the line that establishes the column widths at the end (after loading the data)

Below an example:

With Me.ListBox2
  .ColumnCount = 13
  If Not rst.EOF Then
    .Column = rst.GetRows()
    rst.Close
  Else
    rst.Close
    GoTo CleanUp
  End If
  .ColumnWidths = "3 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;0 cm"
End With

Upvotes: 2

Vityata
Vityata

Reputation: 43595

I also had the same problem - changing size of a listbox without any visible reason. This is how I found this post.

What I did was to rewrite the size of the listbox at the end of the sub. Like this:

With ThisWorkbook.Worksheets(tbl_input.Name)
    .lbTabsA.Width = 131
    .lbTabsA.Height = 350
end with

Probably this automatic change is a bug in Excel.

Upvotes: 1

Related Questions