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



donbowyer wrote:

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

--
donwb

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson