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.
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.
Dim WS_OrderDetails As Worksheet
Set WS_OrderDetails = Worksheets("Order Details")
lstOrderDetails.ColumnCount = 7
lstOrderDetails.List() = WS_OrderDetails.Range("A1:G100").Value
End Sub
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.
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
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.
Then here is the full VBA code of the UserForm_Initialize event.
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.
Also Read
Important Things You Should Know When Using Scrollbars in VBA Userforms
Fill a Listbox From an Array
