PDA

View Full Version : [SOLVED] Multicolum list box displays incorrect format



p9drm1
06-08-2018, 04:23 PM
I'm reading data from an excel spreadsheet in to a multi-column listbox. There are four fields on my excel spreadsheet that contain "True" or "False". When I read them into my listbox using the following code. The List box displays the value of "True" as a 0 and the value of false as a -1. Any ideas how I how I can get the listbox to display the values as True or False instead of a 0 or -1?

Dim sh As Worksheet
Set sh = Worksheets("Events")
With sh

Set Rng = .Range("A1:L" & .Cells(.Rows.Count, 1).End(xlUp).Row)

End With
Me.ListBox1.ColumnCount = 12
Dim myarray As Variant
myarray = Rng.Resize(, Me.ListBox1.ColumnCount).Value
Me.ListBox1.List = myarray


Thanks,
Darryl

Kenneth Hobs
06-08-2018, 07:15 PM
Welcome to the forum! Change 2,4,6,8 to the column numbers with your boolean values.


Private Sub UserForm_Initialize()
Dim a, r As Range, i As Long, j As Integer

With Worksheets("Events")
Set r = .Range("A1:L" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With


With ListBox1
.ColumnCount = 12
a = r.Value
For i = 1 To UBound(a)
For j = 1 To 12
Select Case j
Case 2, 4, 6, 8
If a(i, j) = True Then a(i, j) = "True"
If a(i, j) = False Then a(i, j) = "False"
Case Else
End Select
Next j
Next i


.List = a
End With
End Sub

p9drm1
06-08-2018, 07:49 PM
Hey Ken,

Thank you so much for your speedy reply. I used your code and it appears that it worked for two of the columns in the list box. I'm trying to figure out why it is not recognizing the other columns that need to be changed. I even stepped through the code but was not able to see why it did not change the other columns. Please see the listbox below.

22399

thanks,
Darryl

p9drm1
06-08-2018, 08:06 PM
Thanks again Ken, I figured it out with the case statement. I really appreciate your help.

regards,
Darryl