Solved: Problem with listbox and date

[Rayman]

I attached a simple example file.
It contains a form with 1 listbox and 1 textbox
In the code i fill the list box with a date write in textbox in this case 12/09/11 (italian format: 12 september 2011)
In sheet "A", cell (1,1) the code write the value of listbox, in cell (2,1) the code write the value of textbox.

Result is:
In first cell the value is 09/12/11 (listbox)
in second cell the value is 12/09/11 (textbox)

Any help in avoid this will be much appreciate.

Thanks in advance

[Rob342]

Rayman

Not my strongest subject dates! format the date on sheet 1 col A, also have removed date from lst1 properties
try this:


Option Explicit
Private Sub cmd1_Click()
Lst1.AddItem Txt1.Value
Sheets("A").Cells(1, 1) = Txt1.Value
Sheets("A").Cells(2, 1) = Txt1.Value
End Sub
Private Sub UserForm_Initialize()
Txt1.Value = Format(Date, "dd/mm/yy")
End Sub

[Rayman]

Rayman

Not my strongest subject dates! format the date on sheet 1 col A, also have removed date from lst1 properties
try this:


Option Explicit
Private Sub cmd1_Click()
Lst1.AddItem Txt1.Value
Sheets("A").Cells(1, 1) = Txt1.Value
Sheets("A").Cells(2, 1) = Txt1.Value
End Sub
Private Sub UserForm_Initialize()
Txt1.Value = Format(Date, "dd/mm/yy")
End Sub



Thanks for reply Rob,

but i need to write in the sheet the date taken from Lst1.list not from txt1.
In my real project i write more than one date , a list of date...
The only problem is that the txt1 write in lst1 the date in the correct format, the Lst1 write the date in the sheet in the wrong format....

I am out of idea...

Thanks in anyway and sorry for my poor english.:dunno

[Rob342]

Rayman
Revamped , the value of the txt1 is the same as lst1 unless you change the value in txt1 everytime?

Option Explicit
Private Sub cmd1_Click()
Dim Lst1 As String
Me.Lst1.AddItem Me.Txt1.Value
Me.Lst1 = Me.Txt1.Value
Sheets("A").Cells(1, 1) = Me.Lst1.Value
Sheets("A").Cells(2, 1) = Me.Lst1.Value
End Sub
Private Sub UserForm_Initialize()
Me.Txt1.Value = Format(Date, "dd/mm/yy")
End Sub

[Rayman]

Rayman
Revamped , the value of the txt1 is the same as lst1 unless you change the value in txt1 everytime?

Option Explicit
Private Sub cmd1_Click()
Dim Lst1 As String
Me.Lst1.AddItem Me.Txt1.Value
Me.Lst1 = Me.Txt1.Value
Sheets("A").Cells(1, 1) = Me.Lst1.Value
Sheets("A").Cells(2, 1) = Me.Lst1.Value
End Sub
Private Sub UserForm_Initialize()
Me.Txt1.Value = Format(Date, "dd/mm/yy")
End Sub



I have a lstbox with a list of products in one column, list of product prices in column 2 ,in column3 i have the date of the price.

I select a product in a combobox , in a txtbox i write the new price and in txt i write the new date of use of that new prices.
These new dates are updating the listbox , and the code write in a sheet the new price and the new dates of use of that prices taken from the listbox.
But , if i write 12 september 2011 in the txtbox ( in number, italian format: 12/09/2011) if i want the same date written in the sheet, and not 9 dicember 2011, i have to force the code to format the list box "mm/dd/yyyy". In this way i see in the listbox 09/12/2011 but in the sheet i have the correct date 12/09/2011.

I hope my poor english let you understand...

Thanks again

[Rob342]

Rayman
Dont quite understand, post a copy of your wrkbook

[Aflatoon]

It will not work if you assign the entire list to a sheet in one pass, since VBA will default to a US format. You will need to iterate through the list and use CDate:
Sheets("A").Cells(1, 1) = CDate(Lst1.List(0))

for example.

[Rayman]

It will not work if you assign the entire list to a sheet in one pass, since VBA will default to a US format. You will need to iterate through the list and use CDate:
Sheets("A").Cells(1, 1) = CDate(Lst1.List(0))

for example.

Many thanks Aflatoon, all is working now , Cdate make the job:thumb

Also thanks to Rob for reply