list box date i

[Patriot2879]

hi my listbox data from the textbox1 the date is in american format how do i force it to show dd/mm/yy? the code below is for my textbox1 and the code below that is for my listbox1 hope you can help please?

Private Sub TextBox1_Change()


    Dim ascii   As Integer
    Dim d       As Integer
    Dim m       As Integer
    Dim n       As Integer
    
        n = Len(TextBox1.Value)
        
        If n = 0 Then Exit Sub
        
            ascii = Asc(Right(TextBox1.Value, 1))
            
            ' Validate the day is 2 digits from 01 to 31.
            If n = 2 And ascii <> 47 And CharCode <> 8 Then
                d = CInt(Left(TextBox1.Value, 2))
                If d < 1 Or d > 31 Then
                    MsgBox "Invalid day number " & d & vbLf & "Days are from 01 to 31."
                    TextBox1.Value = ""
                    Exit Sub
                End If
            End If
            
            ' Validate the month is 2 digits from 01 to 12.
            If n = 5 And ascii <> 47 And CharCode <> 8 Then
                m = CInt(Mid(TextBox1.Value, 4, 2))
                If m < 1 Or m > 12 Then
                    MsgBox "Invalid month number " & m & vbLf & "Months are from 01 to 12."
                    TextBox1.Value = Left(TextBox1.Value, 3)
                End If
            End If
                      
End Sub

Private Sub ListBox1_AfterUpdate()
ListBox2.ListIndex = ListBox1.ListIndex

   Me.TextBox1.Value = Me.ListBox1.Column(0)
    Me.TextBox2.Value = Me.ListBox1.Column(1)
     Me.TextBox3.Value = Me.ListBox1.Column(2)
     Me.TextBox4.Value = Me.ListBox1.Column(3)
     Me.TextBox5.Value = Me.ListBox1.Column(4)
     Me.TextBox6.Value = Me.ListBox1.Column(5)
     Me.TextBox7.Value = Me.ListBox1.Column(6)
     Me.TextBox10.Value = Me.ListBox2.Column(0)
     Me.TextBox11.Value = Me.ListBox2.Column(1)
     Me.TextBox12.Value = Me.ListBox2.Column(2)
     Me.TextBox13.Value = Me.ListBox2.Column(3)
     Me.TextBox14.Value = Me.ListBox2.Column(4)
     Me.TextBox15.Value = Me.ListBox2.Column(5)
     Me.TextBox16.Value = Me.ListBox2.Column(6)
     Me.TextBox17.Value = Me.ListBox2.Column(7)
     Me.TextBox18.Value = Me.ListBox2.Column(8)
     Me.TextBox19.Value = Me.ListBox2.Column(9)
 End Sub

 

[Momentman]

I am not certain which you want to format, the listbox or Textbox, was not too clear to me

Why don't you use the format functionto parse it in the format you want

Just a sample

A = format(Textbox1.Value, "dd/mm/yyyy")

 

[Patriot2879]

sorry i wasnt clear its the listbox i need to format to dd/mm/yy. i think it is this part below that i need to change somehow

Me.TextBox1.Value = Me.ListBox1.Column(0)

this is part of the code from my listbox

 

[Momentman]

sorry i wasnt clear its the listbox i need to format to dd/mm/yy. i think it is this part below that i need to change somehow

Me.TextBox1.Value = Me.ListBox1.Column(0)

this is part of the code from my listbox

This line will set the Value in the Textbox to be equal to the value in the Listbox

if you do this, then the Textbox would show the Right format

Me.TextBox1.Value = Format(Me.ListBox1.Column(0),"dd/mm/yyyy")

but if you want to change the value in the listbox, then the code you should be looking for is one when you are writing values to the Listbox

 

[Patriot2879]

sorry i am really confused i just tried this but unfortunately the date format didnt change in the listbox, i am still learning vba, hope you can still advise.

 

[Patriot2879]

Hope you can still advise please?

 

[NoSparks]

You set the format for the time shown in the listbox in UserForm_Initialize.
Why not put in another loop and set the format for the date there also.

 

[Patriot2879]

How do I do that please?

 

[Patriot2879]

Hope you can still help please? Sorry but I am still learning and dont know how to do what you advise.

 

[NoSparks]

Working with the file you previously linked to.
In the form Initialization you format date/time (second column) of the listbox by looping through it.
You should be able to do the formatting of the date in the same manner.

     'format the time in second column
      For i = 0 To .ListCount - 1
         .List(i, 1) = Format(.List(i, 1), "hh:mm Am/pm")
      Next i
    'format the date as dd/mm/yyyy in first column
    For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
    Next i