Prevent horizontal scrolling on listbox - SOLVED

DCPAus

Board Regular
Joined
Nov 7, 2002
Messages
90
I use the code below to fill a listbox, th next problem is that the listbox is shown with a horizontal scroll bar, even though the Width is 60, and the values shown are no large than 3 characters. How can I control the list box so that no horizontal scrollbar is shown.

Private Sub LoadY()
With Me.lBoxY
.Clear
Ylist = ThisWorkbook.Sheets(MENU_SHEET).Range("K1:K3").Value
Ylist = Application.WorksheetFunction.Transpose(Ylist)

For i = 1 To UBound(Ylist)
.AddItem Ylist(i)
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
.Width = 60
End With

End Sub

I solved this by using the TRIM function to ensure that I was only filling using the characters in the column and not empty spaces.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,026
Messages
5,856,928
Members
431,838
Latest member
mydayisgood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top