Solved: Formula to Script

[Emoncada]

I have this Formula that I would like to change it somehow to work for a UserForm. How can I have it do the same thing just in a form.

=IF(LEN(B8)=6,"SERVICE REQUEST",(IF(B8<>"",IF(ISNUMBER(SEARCH("DPT",B8)),"DEPOT",
IF(ISNUMBER(SEARCH("EC",B8)),"PCAR 2007",
IF(ISNUMBER(SEARCH("AP",B8)),"PCAR 2007","VAM"))),"")))

Instead of "B8" I want it to TxtOrderNum.Value

[Emoncada]

this would then send what ever the result is to
CmbBoxProject.Text

So TxtOrderNum would = what ever the user inputs.
CmbBoxProject = The Formula's result.

[Bob Phillips]

With TxtOrderNum
If LEN(.Text)= 6 Then
CmbBoxProject.Value = "SERVICE REQUEST"
ElseIf .Text <> ""
If Instr("DPT",.Text) > 0 Then
CmbBoxProject.Value = "DEPOT"
ElseIf Instr("EC",.Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007",
ElseIf Instr("AP",.Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007"
Else
CmbBoxProject.Value = "VAM"
End If
End If
End With

[Emoncada]

It's not working for me. I get
ElseIf .Text<> ""
and it doesn't like 2007, This is the exact code I got.
rivate Sub TxtOrderNum_Change()
With TxtOrderNum
If Len(.Text) = 6 Then
CmbBoxProject.Value = "SERVICE REQUEST"
ElseIf .Text <> ""
If InStr("DPT", .Text) > 0 Then
CmbBoxProject.Value = "DEPOT"
ElseIf InStr("EC", .Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007",
ElseIf InStr("AP", .Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007"
Else
CmbBoxProject.Value = "VAM"
End If
End If
End With
End Sub

Any Ideas?

[Emoncada]

I get expected Then

[Emoncada]

Ok I played with it this is what i got
Private Sub TxtOrdNum_AfterUpdate()
With TxtOrdNum
If Len(.Text) = 6 Then
CmbBoxProject.Value = "SERVICE REQUEST"
ElseIf .Text <> "" Then
If InStr("DPT", .Text) > 0 Then
CmbBoxProject.Value = "DEPOT"
ElseIf InStr("EC", .Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007"
ElseIf InStr("AP", .Text) > 0 Then
CmbBoxProject.Value = "PCAR 2007"
Else
CmbBoxProject.Value = "VAM"
End If
End If
End With
End Sub

Only thing It works only if the value is exact so if DPT is the only thing in
TxtOrdNum then i will get "DEPOT". But I would like for it to do a search in
that TxtOrdNum for those letters. Can that be done.
Example so if TxtOrdNum = DPT20070504-JE1421 Then
CmbBoxProject.Value = "DEPOT".

Hope that explains what I am going for.
Any Ideas?

[Bob Phillips]

Sorry, Instr was wrong way around



Private Sub TxtOrdNum_AfterUpdate()
With TxtOrdNum
If Len(.Text) = 6 Then
CmbBoxProject.Value = "SERVICE REQUEST"
ElseIf .Text <> "" Then
If InStr(.Text, "DPT") > 0 Then
CmbBoxProject.Value = "DEPOT"
ElseIf InStr(.Text, "EC") > 0 Then
CmbBoxProject.Value = "PCAR 2007"
ElseIf InStr(.Text, "AP") > 0 Then
CmbBoxProject.Value = "PCAR 2007"
Else
CmbBoxProject.Value = "VAM"
End If
End If
End With
End Sub

[Emoncada]

XLD once again you hit it right on the money. Perfect Thanks A Lot.