Userform Listbox reversing date format from database.

D

dellehurley

Board Regular
Joined
Sep 26, 2009
Messages
171
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code: 
        .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.
VBA Code: 
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?
 

Attachments

  • Userform.jpg
    Userform.jpg
    66.1 KB · Views: 11
  • Untitled.png
    Untitled.png
    18.5 KB · Views: 10

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
When the date is loaded into the listbox or even when you pass the date of a control to the cell, it is put in the English format "mm/dd/yyyy". One option is to change the format with the following:

I also added the format for the amounts, if you don't want it you can delete the lines.

To load the listbox.
VBA Code: 
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;0"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = CDate(.List(i, 0))
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
      Next
    End With

For the event click on the listbox
VBA Code: 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
      
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = CDate(DbWs.Range("A" & i).Value)
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")

      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72,122,122,70,70,0,0"
    End With
  End If
End Sub
 
Upvote 0
DanteAmor said:
When the date is loaded into the listbox or even when you pass the date of a control to the cell, it is put in the English format "mm/dd/yyyy". One option is to change the format with the following:

I also added the format for the amounts, if you don't want it you can delete the lines.

To load the listbox.
VBA Code: 
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;0"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = CDate(.List(i, 0))
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
      Next
    End With

For the event click on the listbox
VBA Code: 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
     
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = CDate(DbWs.Range("A" & i).Value)
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")

      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72,122,122,70,70,0,0"
    End With
  End If
End Sub
Click to expand...
Thanks for the reply but unfortunately I'm still having the same issue. The dates are entered into the database correctly, the listbox is the issue. I also noticed that a Boolean column does not display as true or false but as 1 or 0.
I have cause of the issue but cannot figure out how to fix it.

I recently made a post Intermittent Row Source Issue as per the advice on this thread I changed all my RowSources to Lists. I have not had the debug issue since but that is where the date is going wrong.
If I change the line from the commented out line to the other line it fixes the issue but then I will have the other issue as per my previous post.

VBA Code: 
With .lstTrans
            If DbLastRow > 1 Then
'                .List = Range("Database").Value
                .RowSource = "Database!Database"
            Else
'                .List = DbWs.Range("A2:G2").Value
                .RowSource = "Database!A2:G2"
            End If
End With
 
Upvote 0
Try this:

Rich (BB code): 
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;0"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
      Next
    End With

Rich (BB code): 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
      
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = Format(DbWs.Range("A" & i).Value, "dd/mm/yyyy")
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")

      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72,122,122,70,70,0,0"
    End With
  End If
End Sub
 
Upvote 0
Solution
DanteAmor said:
Try this:

Rich (BB code): 
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;0"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
      Next
    End With

Rich (BB code): 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
     
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = Format(DbWs.Range("A" & i).Value, "dd/mm/yyyy")
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")

      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72,122,122,70,70,0,0"
    End With
  End If
End Sub
Click to expand...
(y) perfect thanks for your help.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
DanteAmor said:
I'm glad to help you. Thanks for the feedback.
Click to expand...
Sorry one quick question. How do I format the Boolean back to Boolean.
I tried this below, it recognises it as true but displays it as 0 or -1 even with this code.
VBA Code: 
Dim NoEnd as Boolean
NoEnd = BgtWs.Range("C" & RowNo).Value
               If NoEnd = False Then 'originally I tried 0 and 1 and that did not work either
                    .List(0, 2) = False
                ElseIf NoEnd = True Then
                    .List(0, 2) = True
                End If
 
Upvote 0
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

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

VBA Code: 
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
 
Upvote 0
In the listbox the values are stored as text.
According to your image the true/false values are in column "G"

1654173527217.png


Then:

Rich (BB code): 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
      
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = Format(DbWs.Range("A" & i).Value, "dd/mm/yyyy")
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")
      .List(0, 6) = IIf(DbWs.Range("G" & i).Value = -1, "TRUE", "FALSE")
      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72;122;122;70;70;0;50"
    End With
  End If
End Sub

Private Sub UserForm_Activate()
  Dim DbLastRow As Long
  Dim i As Long
  
  Set DbWs = Sheets("DB")
  DbLastRow = DbWs.Range("A" & Rows.Count).End(3).Row
  
  With Me
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;50"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
        .List(i, 6) = IIf(.List(i, 6) = -1, "TRUE", "FALSE")
      Next
    End With
  End With
End Sub
 
Upvote 0
DanteAmor said:
In the listbox the values are stored as text.
According to your image the true/false values are in column "G"

View attachment 66164

Then:

Rich (BB code): 
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
    With Me.lstSelected
      .Height = 18
      .Width = 470
      .Font.Bold = True
     
      .List = DbWs.Range("A" & i, "G" & i).Value
      .List(0, 1) = Format(DbWs.Range("A" & i).Value, "dd/mm/yyyy")
      .List(0, 3) = Format(DbWs.Range("D" & i).Value, "$#,##0.00")
      .List(0, 4) = Format(DbWs.Range("E" & i).Value, "$#,##0.00")
      .List(0, 6) = IIf(DbWs.Range("G" & i).Value = -1, "TRUE", "FALSE")
      .ColumnCount = 7
      .ColumnHeads = False
      .ColumnWidths = "72;122;122;70;70;0;50"
    End With
  End If
End Sub

Private Sub UserForm_Activate()
  Dim DbLastRow As Long
  Dim i As Long
 
  Set DbWs = Sheets("DB")
  DbLastRow = DbWs.Range("A" & Rows.Count).End(3).Row
 
  With Me
    .lblMths.Caption = "Upcoming Transactions."
    With .lstTrans
      .Visible = True
      .Width = 470
      .Height = 200
      .ColumnCount = 7
      .ColumnHeads = False
      .TextAlign = fmTextAlignLeft
      .ColumnWidths = "70;130;120;70;70;0;50"
      If DbLastRow > 1 Then
        .List = Range("Database").Value
      Else
        .List = DbWs.Range("A2:G2").Value
      End If
      '
      For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
        .List(i, 3) = Format(.List(i, 3), "$#,##0.00")
        .List(i, 4) = Format(.List(i, 4), "$#,##0.00")
        .List(i, 6) = IIf(.List(i, 6) = -1, "TRUE", "FALSE")
      Next
    End With
  End With
End Sub
Click to expand...
OMG that must have been the rowsource screenshot I uploaded.
I got it to work, with a combination of the above
Thanks for the reply.
VBA Code: 
        With .lstDatabase
            .ColumnCount = 14
            .ColumnWidths = "55,50,50,25,50,50,100,80,70,0,0,0,0,0"
            If BgtLastRow > 1 Then
                .List = BgtWs.Range("BudgetList").Value
            Else
                .List = BgtWs.Range("A2:N2").Value
            End If
            For i = 0 To .ListCount - 1
                .List(i, 0) = Format(.List(i, 0), "dd/mm/yyyy")
                .List(i, 1) = Format(.List(i, 1), "dd/mm/yyyy")
                .List(i, 11) = Format(.List(i, 11), "dd/mm/yyyy")
                .List(i, 8) = Format(.List(i, 8), "$#,##0.00")
                If .List(i, 2) = "True" Then
                    .List(i, 2) = "True"
                Else: .List(i, 2) = "False"
                End If
            Next
        End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Userform textbox format when populated by listbox
Replies
9
Views
371
dmt32
D
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
133
Domenic
D
T
  • Solved
VBA UserForm date format reversal dd/mm/yyyy into mm/dd/yyyy
Replies
3
Views
774
6StringJazzer
6StringJazzer
M
  • Solved
VBA Date issue
Replies
3
Views
81
dmt32
D
J
  • Question
Dates Not Copying Correctly
2
Replies
13
Views
346
MARK858
MARK858

Forum statistics

Threads
1,214,848
Messages
6,121,917
Members
449,055
Latest member
KB13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top