How to Format a ListBox column

[Don Bowyer]

Thanks for your input Pascal. No luck - it didn't change
the format of the ListBox.
My problem again....

I have a 2 column ListBox 1st dates 2nd text. ListBox is
populated from an array, itself populated from a 2 column
range of cells in the WorkSheet. The 1st worksheet (dates)
column is formatted in the English (GB) format "31/02/04"
However the ListBox shows the dates in the American longer
format "2/31/2004" (without the leading zero for the
month)
How do I format the ListBox to reproduce the dates as they
were in the original source data.
Any suggestions would be greatly appreciated.
Don Bowyer

 

[Juan Pablo Gonzalez]

How do you populate the listbox ?

 

[Guest]

Hi Juan Pablo
The source data is in "Worksheet2" & consists of three
columns. The first is RangeName "MyBaseDate" containing
dates in form "dd,mm,yy" , the second is associated text
and the third is RangName "MyAlarms". In "MyAlarms" some
cells are blank and some contain an identifier to indicate
an alarm is required on the corresponding date in
column1 "MyBaseDate".

The code below populates a two column ListBox with the
date and associated text of each "alarmed" date.

It works ok, but lists the dates in the US
format "mm,dd,yyyy" instead of in the source
format "dd,mm,yy"

I've tried the Format function but nothing changes.

Here's the code used to populate the ListBox.......

Private Sub ListBox1_Enter()
Count = 0
ListBox1.ColumnCount = 2
ListBox1.TextColumn = 2

Dim MyArray(50, 2) As Variant

Worksheets(2).Activate

For Each cc In Range("MyAlarms")

If cc = "" Then
GoTo Line2
End If
Count = Count + 1
MyDate = cc.Offset(rowOffset:=0, columnOffset:=-2)
MyMessage = cc.Offset(rowOffset:=0, columnOffset:=-1)
MyArray(Count, 0) = MyDate
MyArray(Count, 1) = MyMessage
Line2:
Next cc

ListBox1.List() = MyArray
ListBox1.ColumnWidths = (50)
End Sub

Sorry it's long but that's the story. Any suggestions
would be welcome.
Don Bowyer

 

[Juan Pablo Gonzalez]

Try changing this

MyDate = cc.Offset(rowOffset:=0, columnOffset:=-2)

with

MyDate = cc.Offset(rowOffset:=0, columnOffset:=-2).Text

and also, Dim MyDate as String, at the top of the module.

--
Regards,

Juan Pablo González

Hi Juan Pablo
The source data is in "Worksheet2" & consists of three
columns. The first is RangeName "MyBaseDate" containing
dates in form "dd,mm,yy" , the second is associated text
and the third is RangName "MyAlarms". In "MyAlarms" some
cells are blank and some contain an identifier to indicate
an alarm is required on the corresponding date in
column1 "MyBaseDate".

The code below populates a two column ListBox with the
date and associated text of each "alarmed" date.

It works ok, but lists the dates in the US
format "mm,dd,yyyy" instead of in the source
format "dd,mm,yy"

 

[Don Bowyer]

Hi Juan Pablo
As simple as that!! It fixed the problem fine.
Very many thanks - I much appreciate your input.
Regards
Don Bowyer