CODEWITHSUNDEEP
excelvba
Adilet Yessaliyev
Adilet Yessaliyev

Reputation: 3

textbox shows date as number while vlookuping

I've created a userform with listbox that contains several columns and textbox. The textbox value changes when an item in listbox is selected via Application.WorksheetFunction.Vlookup. but instead of date I get the number in textbox. Can someone help me to fix this problem?

Private Sub ListBox_Change()

    Dim lnItem As Long
    Dim ws, ws2 As Worksheet
    Dim rn2 As Range

    Set ws = Workbooks("TOOLS").Worksheets("TOOLS")
    Set ws2 = Workbooks("TOOLS").Worksheets("CONSOLIDATE")
    Set rn2 = ws2.Range("JOURNAL1")

    Me.TextPurchaseDate.Value = Application.WorksheetFunction.Vlookup(Me.ListBox.List(lnItem, 0), rn2, 3, 0)

 End Sub

Upvotes: 0

Views: 338

Answers (1)

Tim Williams
Tim Williams

Reputation: 166970

Dates are stored as numbers in Excel - what you see in a cell is a formatted version of that - you can use Format() to get a text version if that's what's needed

Private Sub ListBox_Change()

    Dim lnItem As Long
    Dim ws, ws2 As Worksheet
    Dim rn2 As Range, res

    Set ws = Workbooks("TOOLS").Worksheets("TOOLS")
    Set ws2 = Workbooks("TOOLS").Worksheets("CONSOLIDATE")
    Set rn2 = ws2.Range("JOURNAL1")

    res = Application.Vlookup(Me.ListBox.List(lnItem, 0), rn2, 3, 0)

    Me.TextPurchaseDate.Value = Format(res, "mm/dd/yyyy") 'for example

End Sub

Upvotes: 1

Related Questions