Sub SetAndLoadList(MySheet As String)
Dim ViewAllArray As Variant
Dim ViewAllRng As Range
Dim LastRow As Long
Dim colcount As Long
Dim i As Long
Dim j As Long

With Sheets(MySheet)

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

colcount = .Range("A1").End(xlToRight).Column
ReDim ViewAllArray(1 To LastRow, 1 To colcount)
For i = 1 To LastRow
For j = 1 To colcount
ViewAllArray(i, j) = .Cells(i, "A").Text
Next j
Next i
Me.ListBox1.ColumnCount = colcount
Me.ListBox1.List = ViewAllArray
End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Incidental" wrote in message
...
Hi all

I was wondering if someone can give me some advice on UK based dates
in a Listbox. I am running Excel 2003 on XP. My problem is that
Excel reverts back to US date formatting when you load the data from a
sheet into a Listbox.

The method I have been using to transfer the data is by passing the
range to an array and then the array to the Listbox, this is because I
want the code to be available to calls to load data from many
different sheets with varying row and column count for example 194
rows and 19 columns with around 16 of those columns holding a date, I
have included a sample of code at the end of the post to show what I’m
doing.

I have read in this group that one way around it is to store the dates
in the sheet as text rather than a date, I am trying to find away to
solve the problem without having to change the formatting used on the
sheet but if I have no other options that is what I shall do.

Any and all help is much appreciated.

Option Explicit
Dim LastRow As Integer
Dim colCount As Integer
Dim ViewAllArray()
Dim ViewAllRng As Range

Sub SetAndLoadList(MySheet As String)
‘Name of the sheet is passed as an argument
With Sheets(MySheet)

LastRow = [A65535].End(xlUp).Row

colCount = [A1].End(xlToRight).Column

Set ViewAllRng = Range(Cells(1, 1), Cells(LastRow, colCount))

ViewAllArray = ViewAllRng

Me.ListBox1.ColumnCount = colCount

Me.ListBox1.List = ViewAllArray

End With

End Sub

Cheers

Steve