dim myRng as range
dim myCell as range

with worksheets("somesheetnamehere")
'just the first column
set myrng = .range(strRange).offset(2,0).resize(3,1)
end with

with me.lstnumberanddate
for each mycell in myrng.cells
.additem mycell.value
.List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
next mycell
end with

(untested, uncompiled. Watch for typos.)

Jac Tremblay wrote:

Hi,
I have read many posts on date formats and many answers from Tom Ogilvy and
others. I learned that if a date in a cell can be interpreted as a US date,
it will. So one can apply a specified format through code like this:
Me.txtDateStart = _
Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
My first problem is now resolved. I have a second one.
I have a two column list of data in the sheet that I load in a list box
through code like this:
Me.lstNumberAndDate.List = _
.Range(strRange).Offset(2, 0).Resize(3, 2).Value
The first column is a number and the second is a date. When I load the data
in the list, the date appears in US format. How can I have it in
international format like the others?
Will I have to split the data in two or load it in two operations? If so,
can someone tell me how?
Thanks.
--
Jac Tremblay

--

Dave Peterson

Hi Dave,
That is exactly the solution I found in the newsgroup. You were faster to
code it than me.
Tomorrow, i will post the exact solution I will use.
Thank you very much. I appreciate.
Good night.
--
Jac Tremblay


"Dave Peterson" wrote:

dim myRng as range
dim myCell as range

with worksheets("somesheetnamehere")
'just the first column
set myrng = .range(strRange).offset(2,0).resize(3,1)
end with

with me.lstnumberanddate
for each mycell in myrng.cells
.additem mycell.value
.List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
next mycell
end with

(untested, uncompiled. Watch for typos.)

Jac Tremblay wrote:

Hi,
I have read many posts on date formats and many answers from Tom Ogilvy and
others. I learned that if a date in a cell can be interpreted as a US date,
it will. So one can apply a specified format through code like this:
Me.txtDateStart = _
Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
My first problem is now resolved. I have a second one.
I have a two column list of data in the sheet that I load in a list box
through code like this:
Me.lstNumberAndDate.List = _
.Range(strRange).Offset(2, 0).Resize(3, 2).Value
The first column is a number and the second is a date. When I load the data
in the list, the date appears in US format. How can I have it in
international format like the others?
Will I have to split the data in two or load it in two operations? If so,
can someone tell me how?
Thanks.
--
Jac Tremblay

--

Dave Peterson

Hi Dave,
As I said yesterday, here is the code I use:
Dim rngCell As Excel.Range
Dim rng1stCol As Excel.Range
Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _
.Range(strRange).Offset(2, 0).End(xlDown))
For Each rngCell In rng1stCol
Me.lstNumberAndDate.AddItem rngCell.Value
Me.lstNumberAndDate.List(Me.lstNumberAndDate.ListC ount - 1, 1) = _
Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd")
Next rngCell
It works in Exce 2000 and 2007.
Thanks again for your precious comment.
--
Jac Tremblay


"Dave Peterson" wrote:

dim myRng as range
dim myCell as range

with worksheets("somesheetnamehere")
'just the first column
set myrng = .range(strRange).offset(2,0).resize(3,1)
end with

with me.lstnumberanddate
for each mycell in myrng.cells
.additem mycell.value
.List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
next mycell
end with

(untested, uncompiled. Watch for typos.)

Jac Tremblay wrote:

Hi,
I have read many posts on date formats and many answers from Tom Ogilvy and
others. I learned that if a date in a cell can be interpreted as a US date,
it will. So one can apply a specified format through code like this:
Me.txtDateStart = _
Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
My first problem is now resolved. I have a second one.
I have a two column list of data in the sheet that I load in a list box
through code like this:
Me.lstNumberAndDate.List = _
.Range(strRange).Offset(2, 0).Resize(3, 2).Value
The first column is a number and the second is a date. When I load the data
in the list, the date appears in US format. How can I have it in
international format like the others?
Will I have to split the data in two or load it in two operations? If so,
can someone tell me how?
Thanks.
--
Jac Tremblay

--

Dave Peterson