Userform Listbox reversing date format from database.

[dellehurley]

Hi,
Can someone tell me why my listbox suddenly started flipping the date format form dd/mm/yyyy to mm/dd/yyyy. Obviously I must have changed something but I cannot figure out what
The database which is the source shows the correct format.

This is the listbox related part of my reset procedure for the userform

        .lblMths.Caption = "Upcoming Transactions."
        .lstTrans.Visible = True
        .lstTrans.Width = 470
        .lstTrans.Height = 200
        .lstTrans.ColumnCount = 7
        .lstTrans.ColumnHeads = False
        .lstTrans.TextAlign = fmTextAlignLeft
        .lstTrans.ColumnWidths = "70,130,120,70,70,0,0"
        If DbLastRow > 1 Then
            .lstTrans.List = Range("Database").Value
        Else
            .lstTrans.List = DbWs.Range("A2:G2").Value
        End If

This is the code behind the listbox. This selects the row clicked on a shows it in a single line listbox. This too now shows the date incorrectly.

Private Sub lstTrans_Click()
Dim i As Long

    Me.txtRowNo.Value = Val(Me.lstTrans.ListIndex + 1)
    i = Me.txtRowNo.Value + 1
    Me.lstTrans.ColumnHeads = False
    If Me.lstTrans.ListIndex <> -1 Then
        Me.lstSelected.Height = 18
        Me.lstSelected.Font.Bold = True
        Me.lstSelected.List = DbWs.Range("A" & i, "G" & i).Value
        Me.lstSelected.ColumnCount = 7
        Me.lstSelected.ColumnHeads = False
        Me.lstSelected.ColumnWidths = "72,122,122,70,70,0,0"
    End If

The database is simple and userform, a screenshot below
Any ideas?

 

[dellehurley]

You could consider trying to read each cell in your range into a variant array as Text - This should preserve how the cell is formatted & hopefully, display correctly in your listbox

Me.lblMths.Caption = "Upcoming Transactions."
    With Me.lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;0"
  
      .List = GetList(Range("DataBase"))
    
    End With

Place following in a STANDARD module

Function GetList(ByVal Target As Range) As Variant
    Dim arr()       As Variant
    Dim r           As Long, c As Long
  
    ReDim arr(1 To Target.Rows.Count, 1 To Target.Columns.Count)
  
    For r = 1 To UBound(arr, 1)
        For c = 1 To UBound(arr, 2)
            arr(r, c) = Target.Cells(r, c).Text
        Next c
    Next r
    GetList = arr
End Function

Code not tested but if suggestion does what you want you can use function in your project for other listboxes if need to overcome similar issues.

Hope Helpful

Dave

I will definitely look into this, I think it will be a huge improvement from what I currently have.
Thanks for the reply.
Dannielle