Hi all,

I have a userform in which a listbox resides. The user has to select an item on the listbox, fill in the userform and then the data entered will be populated against the item selected. The columns that are pulled into my listbox are columns 2,4,7,8. They are all text apart from column 8, which is the date. In the spreadsheet that the listbox pulls from, the date is in UK format. However it displays as US format inside the listbox which is very confusing for the user as we live in the UK and are used to dd/mm/yyyy! I need to be able to change it to the UK format!

The code to the userform is below (the listbox population bit is at the end):

Option Explicit

Private Sub cboTracker_Change()

End Sub

Private Sub cmdClear_Click()
Dim ctl As MSForms.Control

    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl

End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
Dim ws As Worksheet
Dim iRow As Long
Dim RowCOunt As Long



    If Not IsDate(Me.txtdtDateFollow.Value) Then
        MsgBox "The Date booked box must contain a date.", vbExclamation, "Post Appointment Entry Form Error"
        Me.txtdtDateFollow.SetFocus
        Exit Sub
    End If

    If Me.cboTracker.Value = "" Then
        MsgBox "Please enter the initials of the person whom you would like to track this follow up.", vbExclamation, "Post Appointment Entry Form Error"
        Me.cboTracker.SetFocus
        Exit Sub
    End If

    If Me.txtFollowUp.Value = "" Then
        MsgBox "Please enter the follow up actions that are required.", vbExclamation, "Post Appointment Entry Form Error"
        Me.txtFollowUp.SetFocus
        Exit Sub
    End If

    If Me.txtPostContent.Value = "" Then
        MsgBox "Please enter in the content of the appointment. This should contain what the appointment was about/its purpose, what was demoed and how the appointment was received.", vbExclamation, "Post Appointment Entry Form Error"
        Me.txtPostContent.SetFocus
        Exit Sub
    End If
    
      If lstAppointment.ListIndex = -1 Then 'checked but nothing selected in listbox
 MsgBox "You must select the appointment you just had.", , "Missing Info" 'select a mode since you checked the box
 lstAppointment.SetFocus
    Exit Sub
 End If
    
    
        Set ws = Worksheets("Sheet1")

    iRow = lstAppointment.List(lstAppointment.ListIndex, 4)
    

    RowCOunt = Worksheets("Sheet1").Range("J3").CurrentRegion.Rows.Count
    With Worksheets("Sheet1").Range("J3")
        ws.Cells(iRow, 10).Value = Me.txtPostContent.Value
        ws.Cells(iRow, 11).Value = Me.txtFollowUp.Value
        ws.Cells(iRow, 12).Value = DateValue(Me.txtdtDateFollow.Value)
        ws.Cells(iRow, 13).Value = Me.cboTracker.Value

    End With
    
    Unload Me

End Sub

Private Sub txtdtDateFollow_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Me.txtdtDateFollow.Value = Format(Me.txtdtDateFollow, "dd/mm/yyyy")
End Sub


Private Sub lstAppointment_Click()

End Sub

Private Sub txtFollowUp_Change()

End Sub

Private Sub txtPostContent_Change()

End Sub


Private Sub UserForm_Initialize()
Dim rng As Range, r As Range
Dim LastRow As Long
Dim I As Long

    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("H3:H1302"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    With Worksheets("Sheet1")
        LastRow = .Range("H" & Rows.Count).End(xlUp).Row
        
        .Range("A4:N" & LastRow).AutoFilter Field:=8
        
        
        Set rng = .Range(.Cells(4, 1), .Cells(LastRow, 1))
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        
        ReDim rtab(0 To rng.Count - 1, 1 To 5)

        For Each r In rng
            rtab(I, 1) = r.Offset(0, 1)
            rtab(I, 2) = r.Offset(0, 3)
            rtab(I, 3) = r.Offset(0, 6)
            rtab(I, 4) = r.Offset(0, 7)
            rtab(I, 5) = r.Row
            I = I + 1
        Next
    End With
    


    With lstAppointment
        .ColumnCount = 5
        .ColumnHeads = False
        .ColumnWidths = "70 pt;100pt;20pt;30pt;0 pt"
        .List = rtab
    End With
    
    
End Sub
I have tried adding this in as the end of the code after the listbox is populated, but it is not working...any other ideas would be greatly appreciated!

For I = 0 To lstAppointment.ListCount-1
       lstAppointment.List(I, 7) = Format(DateValue(lstAppointment.List(I,7)), "dd/mm/yyyy")
Next I
Thanks!