Embedded Files
'Input Box
RetryContractNumb:
ContractNumber = InputBox("Enter the OLD Contract #. Ex: 123-1234567-123", "Inputbox")
If StrPtr(ContractNumber) = 0 Then
'Option 1 Cancel
osCurrentScreen.SendKeys "COMPLETED"
osCurrentScreen.SendControlKey ControlKeyCode_Enter
osCurrentScreen.SendKeys "NO"
For x = 1 To 3
osCurrentScreen.SendControlKey ControlKeyCode_Enter
Next x
Exit Sub
Else
If ContractNumber = "" Then
' Option 2 Empty
MsgBox "You entered nothing. Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
Else
'Option 3 Not Empty
'Check Lenght 15 Characters
If Len(ContractNumber) > 15 Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
If Len(ContractNumber) < 15 Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
'Check Lessor for Hyphen
LContractNumber = Left(ContractNumber, 3)
If LContractNumber Like "*-*" Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
'Check Schedule for Hyphen
RContractNumber = Right(ContractNumber, 3)
If LContractNumber Like "*-*" Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
'Check Contract for Hyphen
MContractNumber = Mid(ContractNumber, 5, 7)
If MContractNumber Like "*-*" Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
'Remove hyphens
ContractNumber = Replace(ContractNumber, "-", "")
'Check Numbers only
If IsNumeric(ContractNumber) Then
GoTo NextStep1
Else
MsgBox "Not Numeric or It has Spaces. Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 123-1234567-123."
GoTo RetryContractNumb
End If
End If
End If
NextStep1:
'Check Lenght 13 Characters
If Len(ContractNumber) > 13 Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 900-1234567-000."
GoTo RetryContractNumb
End If
If Len(ContractNumber) < 13 Then
MsgBox "Contract does not meet the format XXX-XXXXXXX-XXX. Ex: 900-1234567-000."
GoTo RetryContractNumb
End If
'Reconstruct Contract Format
ContractNumber = Replace(ContractNumber, " ", "")
LESSOR = Mid(ContractNumber, 1, 3)
CONTRACT = Mid(ContractNumber, 4, 7)
SCHEDULE = Mid(ContractNumber, 11, 3)
ContractNumber = LESSOR & "-" & CONTRACT & "-" & SCHEDULE
'Input Box End
Page updated
Google Sites
Report abuse