Pages

Add Horizontal Scrollbar to a Listbox

Today I’m going to discuss about adding a horizontal scrollbar to a listbox. You might think that you can add this from the Properties window of the VBA editor. But it is not. You can’t change a property and add a horizontal scrollbar for a VBA listbox. Here is a sample listbox and available properties in the properties window.

Sample listbox


Properties window of a listbox

As you can see there is no property associated with the horizontal scrollbars. So how do we add one to the listbox? Actually you don’t need to do anything to add a horizontal scrollbar. Scrollbar will be automatically added to the VBA listbox if the space is not enough to show all the columns. But the automatic appearance of the horizontal scrollbar is not smooth. I will show an example of malfunction and explain how to solve it at the end of this post.

Here is an example where scrollbars were added automatically to the listbox.

Private Sub UserForm_Initialize()

     Dim WS_OrderDetails As Worksheet

     Set WS_OrderDetails = Worksheets("Order Details")

     lstOrderDetails.ColumnCount = 7

     lstOrderDetails.List() = WS_OrderDetails.Range("A1:G100").Value

End Sub
Listbox with both vertical and horizontal scrollbars

But in this next example the horizontal scrollbar doesn’t appear even though it is needed. So the user can’t see some of the information.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub
Horizontal scrollbar of the listbox didn’t appear as expected.

Only the vertical scrollbar is available for the above listbox. So the user can’t see the email addresses listed in the VBA listbox. If you get into this kind of problem, you can fix it in two ways. One method is to increase the width of the listbox. For that sometimes you may need to increase the width of your VBA form as well. However if you are unable to increase width due to design aspect or any other reason then you can follow the second method. Second method is assigning column widths for listbox columns using VBA. You can do it using the ColumnWidths property as follows.

lstNameEmailList.ColumnWidths = "125;125;250"

Then here is the full VBA code of the UserForm_Initialize event.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "125;125;250"

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub

This is the result after the adding the ColumnWidths property.

Horizontal scrollbar added when use the ColumnWidths property

Also Read
Important Things You Should Know When Using Scrollbars in VBA Userforms
Fill a Listbox From an Array