This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

Setting horizontal scrollbars on a UserForm ListBox

Setting horizontal scrollbars UserForm ListBox

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.

The problem

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.

Text in ListBox too wide
ListBox cuts off text

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.

Text in ListBox set scrollbars
ColumnWidths wider than Width creates scrollbars
Text in ListBox with scrollbar too wide
ListBox with scrollbars

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

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.

Text in ListBox invisible TextBox

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:

Text in ListBox with working scrollbar
ListBox with working scrollbar
Text in ListBox with working scrollbar right
ListBox with working scrollbar

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.


Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *