I recently had an issue with a ListBox on a UserForm. It was an issue I wasn’t expecting. I thought I would share the solution I came up with.
I wanted to use a ListBox on a UserForm to show text. But the text could be very short or very long. I wouldn’t know the size of the text in advance. I wanted the ListBox to be a sensible size.
I decided scrollbars were the way to go. Setting up scrollbars is easy, I just needed to make the column width wider than the width of the ListBox.
But, this was still not right. As you can see in the image above, the text has still been cut off.
I needed to know how wide the text was, to set the scrollbar to be the right size. How could I find out the width of the text? I couldn’t just count how many characters there were in each line of the ListBox, because unless I was using a monospaced font, where all the characters are the same size, the width of each character will vary. For example, “W” is much wider than “i”. They are both one character, but they do not have the same width. Having a complex calculation to estimate the width based on the total width of individual characters seemed way too complex.
I knew there must be a better way.
The solution took a bit of thinking.
- I created a TextBox on the UserForm
- I Set the TextBox to be Visible = False and AutoSize = True. This makes an invisible TextBox which expands to fit the size of the text within it.
- I created a variable to hold the maximum width of the TextBox
- I looped through every line of text in the ListBox and entered the value into the TextBox.
- If the width of the TextBox becomes wider than the variable then the variable is updated.
- Once the loop as finished, the variable holds the necessary size of the TextBox.
- I set the width of the ListBox to be equal to the variable.
Here are the ListBox and TextBox I created.
Here is the code I used:
Private Sub UserForm_Activate() 'Hack to get the scroll bars to work Dim i As Integer Dim scrollBarWidth As Double For i = 0 To myListBox.ListCount - 1 myTextBox.Value = myListBox.List(i) If myTextBox.Width > scrollBarWidth Then scrollBarWidth = myTextBox.Width Next i myListBox.ColumnWidths = scrollBarWidth End Sub
Here is the final result:
Is there a better solution?
Have you got another solution which is easier? Please share your knowledge, let us all know by leaving a comment below.