list box date i

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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?

VBA Code:
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

Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,064
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
VBA Code:
A = format(Textbox1.Value, "dd/mm/yyyy")
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
VBA Code:
Me.TextBox1.Value = Me.ListBox1.Column(0)

this is part of the code from my listbox
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,064
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
VBA Code:
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
VBA Code:
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

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

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.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,008
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hope you can still help please? Sorry but I am still learning and dont know how to do what you advise.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,008
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
     '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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,824
Members
410,813
Latest member
Vhinzvirgo
Top