VBA If Statement with Conditions causing issue with Dates

[ian9999]

Hi all

I am looking for any help I can get on the below

I'm trying to write code behind a user form that operates when you selected a contract number via a dropdown list, it then populates the textboxes, comboxes and listboxes where appropriate on the user form.

Some of the fields might either return a date hopefully in UK format DD/MM/YYYY or maybe a word

We are also using a list to change the name of the destination of the data via a counter and vlookup, the below code is from the combobox

The issue we are having is the code isn't populating the boxes correctly, either nothing is appearing or the format of the dates are coming through as numbers

I hope this makes sense, if not please ask and I will explain further

Looking for any advice you could offer

Regards

Ian

Private Sub CboContractNumber_Change()

Dim ContractLive
Dim ContractSP
Dim ContractLiveCount As Integer
Dim ContractSPCount As Integer
Dim Ctrl As Control
Dim Fieldname
Dim Fieldname2
Dim Counter
Dim Textbox
Counter = 1

For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "TextBox" Then
Ctrl.Text = ""
End If
Next Ctrl

ContractSP = Application.WorksheetFunction.IfError(Application.VLookup(CboContractNumber.Value, Sheets("LookupNEW").Range("BA1:BC4000"), 2, False), Application.WorksheetFunction.IfError(Application.VLookup(CboContractNumber.Value, Sheets("LookupNEW").Range("BB1:BC4000"), 1, False), 0))

Do Until Counter > 80

Fieldname2 = Sheets("LookupNEW").Range("BG" & Counter).Value
Textbox = Sheets("LookupNEW").Range("BH" & Counter).Value

Fieldname = WorksheetFunction.Match(Fieldname2, Sheets("Master Data NEW").Range("A2:EZ2"), 0)

If Application.VLookup(ContractSP, Sheets("Master Data NEW").Range("A:EZ"), Fieldname, False) = 0 Then
ElseIf IsDate(Application.VLookup(ContractSP, Sheets("Master Data NEW").Range("A:EZ"), Fieldname, False) = Format("mm/dd/yyyy")) = True Then
Me.Controls(Textbox) = CDate(Application.VLookup(ContractSP, Sheets("Master Data NEW").Range("A:EZ"), Fieldname, False))

Else

Me.Controls(Textbox) = Application.VLookup(ContractSP, Sheets("Master Data NEW").Range("A:EZ"), Fieldname, False)

End If
Counter = Counter + 1
Loop

End Sub

 

[ian9999]

Sorry to ask but any thoughts if the code is incorrectly set out