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.
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.