UK Based data format in ListBoxes

[Incidental]

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

 

[Bob Phillips]

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)

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

 

[Incidental]

Hi Bob

Thanks for your reply it works like a charm, i had thought about
looping through the cells while putting them into the array but
figured it would be much more work than it turned out to be.

Just a point for anyone else who finds this post and uses bob's code,
the line ViewAllArray(i, j) = .Cells(i, "A").Text will fill each
column of the array with the values in column A, to pass all columns
change the line to ViewAllArray(i, j) = .Cells(i, j).

Thanks again for your help

Steve

 

[Bob Phillips]

Sorry about that error. Originally I was just going to use the Text property
on the date column, but then decided to use it on all and moved the code but
didn't update it.

--
HTH

Bob

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

 

[Incidental]

Not to worry i spotted it after the first test run and changed it but
just thought i would mention it for anyone else who comes across the
post and needs to use your code as it has proved very useful to me i'm
sure it will do the same for many others searching the same problem.
Thanks again for your help.

Steve