Vba date conversion not working

[drluke]

I am using vlookup to return a date from my spreadsheet. I have tried to convert the date to Long by using CLng & CDate in my code, but I am still getting a type mismatch error.

Here is my code:

Private Sub TextBox9_Change()
Dim wsJournal As Worksheet
Dim MyDate As Long

    Set wsJournal = Worksheets("Journal")
    MyDate = CLng(CDate(Me.tbDate.Value))
    
    MyDate = Application.VLookup(Me.tbPeriod, [YrDte], 1, False)
    
End Sub

Any advice would be appreciated.

 

[mikerickson]

Which line is causeing that error
The Vlookup might not be returning a Long data type, it might be Double or Error or String.

 

[drluke]

Debugger highlights this row: MyDate = Clng .........

 

[mikerickson]

try

MyDate = CLng(DateValue(Me.tbDate.Text))

 

[drluke]

Still have the type mismatch error.

 

[MARK858]

What happens if you change

Dim MyDate As Long

to

Dim MyDate As Date

 

[drluke]

I still get the Type mismatch error, but when debugging MyDate = 00:00:00 and Me.tbDate.Value or Me.tbDate.Text = ""

 

[MARK858]

That looks more like a time than a date, try dimming as double.

 

[drluke]

Frustratingly, still the same error!

 

[Akuini]

I still get the Type mismatch error, but when debugging MyDate = 00:00:00 and Me.tbDate.Value or Me.tbDate.Text = ""

Why Me.tbDate.Value is empty? Is Me.tbDate actually has value but the code somehow can't get the value?
One possibility for the Type mismatch error is it Me.to Date.Value is not a date type or can't be treated as date type.