Double Click Event Changes Date

[ExcelEndeavor]

I have a double-click event that opens a userform where data can be edited. I don't want the Invoice Number to be edited, so I added the line "AddInvoiceForm.TxtInvoiceNumber.Enabled = False" to disable it, but now the Invoice Date returns as MS date code. See the before and after screenshots below. How do I get the date to return correctly again?

Private Sub lbxInvoiceList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim selectedRow As Integer
    selectedRow = lbxInvoiceList.ListIndex
  
    If selectedRow >= 0 Then
        AddInvoiceForm.TxtInvoiceNumber.Value = lbxInvoiceList.List(selectedRow, 0)
            AddInvoiceForm.TxtInvoiceNumber.Enabled = False
        AddInvoiceForm.TxtInvoiceDate.Value = lbxInvoiceList.List(selectedRow, 1)
        AddInvoiceForm.TxtInvoiceAmount.Value = lbxInvoiceList.List(selectedRow, 2)
        AddInvoiceForm.Show
      
    End If
  
End Sub

Before I added the code:

After I added the code:

 

[RoryA]

Use CDate:

AddInvoiceForm.TxtInvoiceDate.Value = CDate(lbxInvoiceList.List(selectedRow, 1))

Note: if all these controls are in the same form, you should omit the reference to AddInvoiceForm

 

[ExcelEndeavor]

Thank you for your quick response, but that didn't work. iIt still returns the same MS result (2nd screenshot above). Also, the controls are not the same form, so the reference is necessary.

 

[RoryA]

You've obviously got some other code interfering then, since the code you posted would not produce the results you showed. What is in the Textbox events?

 

[ExcelEndeavor]

It only started doing this when I added the code to disable the field above it (AddInvoiceForm.TxtInvoiceNumber.Enabled = False)

I have the following textbox event:

Private Sub TxtInvoiceDate_AfterUpdate()
 
    TxtInvoiceDate.Text = Format(TxtInvoiceDate, "##/##/####")
 
    If IsDate(TxtInvoiceDate.Text) Then
 
Else
    MsgBox "Please Enter A Valid Date", vbInformation
    TxtInvoiceDate.Text = Empty

 

[RoryA]

That code doesn't make a lot of sense as written. It should be something like (adjust the date formatting as needed):

Private Sub TxtInvoiceDate_AfterUpdate()
 
If IsDate(TxtInvoiceDate.Text) Then
    TxtInvoiceDate.Text = Format(TxtInvoiceDate, "dd/mm/yyyy")
 
Else
    MsgBox "Please Enter A Valid Date", vbInformation
    TxtInvoiceDate.Text = Empty
End If
End Sub

 

[ExcelEndeavor]

Thank you - that corrected the issue, but not it forces a leading 0 - instead of 3/9/2025, it says 03/09/2025. Is there a way to fix that?

 

[RoryA]

Just change the format code. If you only want single digit day or month numbers, use d or m rather than dd or mm

 

[ExcelEndeavor]

Great - thank you!

 

[RoryA]

Glad we could help.