CODEWITHSUNDEEP
vba
Prabhat Vishwas
Prabhat Vishwas

Reputation: 43

How to get date value to text box from a column instead of date serial?

I created a tiny user-form where TextBox1 is txtsr and TextBox2 is txtdate. and the fourth column of my Vlookup table are Date Column, all cells are filled with date (dd-mm-yyyy) format.

With below code when I type an text in txtsr field, the txtdate is filled with date serial number instead of Date format.

Private Sub txtsr_Change()

Dim myRange As Range
Set myRange = Worksheets("VEHICLE IN").Range("B2:F20")

txtdate.Value = Application.WorksheetFunction.VLookup(txtsr, myRange, 4, False)
If Err.Number <> 0 Then txtdate.Value = ""

End Sub

how to show txtdate filled with correct date format (dd-mm-yyyy) instead of sate serial number?

Upvotes: 0

Views: 243

Answers (1)

Variatus
Variatus

Reputation: 14383

This ought to do the job (untested - please try).

Private Sub txtsr_Change()

    Dim myRange As Range
    Dim MyDate As Variant
    
    Set myRange = Worksheets("VEHICLE IN").Range("B2:F20")
    MyDate = Application.WorksheetFunction.VLookup(txtsr, myRange, 4, False)
    If Err.Number Then
        txtdate.Value = ""
    Else
        txtdate.Value = Format(MyDate, "dd-mm-yy")
    End If
End Sub

Upvotes: 1

Related Questions