Reputation: 3
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
Reputation: 166980
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