Format a ListBox column

[donbowyer]

I'm using Office2003 on Win XP
I load a ListBox from a worksheet range via an array.
All works OK but the formatting of one column containing times is lost.
The Range source data format is correct - "hh:mm".
But a time of "09:40" at source becomes "9:4" in the ListBox.
What code can I add to my ListBox formatting to recover the correct format.

 

[Dave Peterson]

Maybe you can modify that column in your array after you pick it up, but before
you assign it to the listbox:

Option Explicit
Private Sub UserForm_Initialize()
Dim myArr As Variant
Dim myRng As Range
Dim rCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a1:d20")
End With

myArr = myRng.Value

For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
myArr(rCtr, 1) = Format(myArr(rCtr, 1), "hh:mm:ss")
Next rCtr

With Me.ListBox1
.ColumnCount = myRng.Columns.Count
.List = myArr
End With

End Sub

 

[donbowyer]

Many thanks for the code Dave - I'll give it a try.
Don Bowyer

 

[donbowyer]

Hi again Dave.
I tried the code in slightly modified form as below.

Count = 0
For Each cc In Range("MyContact")
Count = Count + 1
MyContact = cc.Text
MyTime = cc.Offset(rowOffset:=0, columnOffset:=2).Text
MyPhone = cc.Offset(rowOffset:=0, columnOffset:=4).Text
MyArray(Count - 1, 0) = MyContact
MyArray(Count - 1, 1) = MyTime
MyArray(Count - 1, 1) = Format(MyArray(Count - 1, 1), "hh:mm")
MyArray(Count - 1, 2) = MyPhone
Next cc

This change is at Array level before the data is applied to the ListBox.
The result is that in the ListBox displays a time number
ie 15:53 becomes 0.661805.....
This I don't understand - any ideas would be welcome
Regards
Don Bowyer

 

[Dave Peterson]

Maybe it's what you do after the code you posted.

This works for me:

Option Explicit
Private Sub UserForm_Initialize()
Dim myCount As Long
Dim cc As Range
Dim myTime As String
Dim myPhone As String
Dim myContact As String
Dim myArray() As String

Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("MyContact")

ReDim myArray(0 To myRng.Rows.Count - 1, 0 To 2)

myCount = 0
For Each cc In myRng.Cells
myCount = myCount + 1
myContact = cc.Text
myTime = cc.Offset(rowOffset:=0, columnOffset:=2).Text
myPhone = cc.Offset(rowOffset:=0, columnOffset:=4).Text
myArray(myCount - 1, 0) = myContact
myArray(myCount - 1, 1) = Format(myTime, "hh:mm")
myArray(myCount - 1, 2) = myPhone
Next cc

With Me.ListBox1
.ColumnCount = 3
.List = myArray
End With
End Sub


But since you're picking up the .text property in this line:
myTime = cc.Offset(rowOffset:=0, columnOffset:=2).Text

And if those cells are formatted the way you want, then you don't need to do any
formatting in code.

This will be sufficient:
myArray(myCount - 1, 1) = myTime

Hi again Dave.
I tried the code in slightly modified form as below.

Count = 0
For Each cc In Range("MyContact")
Count = Count + 1
MyContact = cc.Text
MyTime = cc.Offset(rowOffset:=0, columnOffset:=2).Text
MyPhone = cc.Offset(rowOffset:=0, columnOffset:=4).Text
MyArray(Count - 1, 0) = MyContact
MyArray(Count - 1, 1) = MyTime
MyArray(Count - 1, 1) = Format(MyArray(Count - 1, 1), "hh:mm")
MyArray(Count - 1, 2) = MyPhone
Next cc

This change is at Array level before the data is applied to the ListBox.
The result is that in the ListBox displays a time number
ie 15:53 becomes 0.661805.....
This I don't understand - any ideas would be welcome
Regards
Don Bowyer

 

[donbowyer]

Thanks again Dave,
Yes I put your code into a separate project and it worked for me too.
I then went back to my code and found an additional (intermediate) step in
the process of going from array to ListBox which had escaped my notice in
earlier troubleshooting. This step puts the array contents into a range of
worksheet cells. If these are not formatted correctly their default format
"general" prevails and that was giving the problem.
All is now well
Regards
Don Bowyer