Notes

Sub BasicSanityToStartBIFileCreation()
Dim i As Integer
Dim in_payFrom As String
Dim in_payTo As String
Dim in_paymentType As String
Dim in_localFolderPath As String
Dim in_amount As String
Dim in_BIFileName As String
Dim in_CrCurrency As String
Dim var_basicInfo_flag As String


Dim in_FX_Type As String
Dim in_FX_Reference As String
Dim in_FX_Date As String
Dim in_FX_Rate As String
Dim var_FXInfo_flag As String

Dim in_Invoice_Type As String
Dim in_No_Of_Invoices As String
Dim var_InvoiceInfo_flag As String
Dim out_Optional_Message As String
Dim out_Mandatory_Message As String
Dim out_DuplicateTemplate_Message As String

Dim in_TemplateSaved_Flag As String
Dim in_Template_Name As String
Dim var_Duplicate_TemplateName_flag As String
Dim in_File_Format As String

Dim lastrow As Long

var_InvoiceInfo_flag = "False"
var_FXInfo_flag = "False"
var_basicInfo_flag = "False"
var_Duplicate_TemplateName_flag = "False"

Dim tempString As String


in_payFrom = Sheet1.Range("D3").Value
in_payTo = Sheet1.Range("D5").Value
in_localFolderPath = Sheet1.Range("D7").Value
in_paymentType = Sheet1.Range("D9").Value
in_amount = Sheet1.Range("D11").Value
in_CrCurrency = Sheet1.Range("D13").Value
in_BIFileName = Sheet1.Range("D15").Value
in_FX_Type = Sheet1.Range("D18").Value
in_FX_Reference = Sheet1.Range("D20").Value
in_FX_Date = Sheet1.Range("D22").Value
in_FX_Rate = Sheet1.Range("D24").Value
in_Invoice_Type = Sheet1.Range("D27").Value
in_No_Of_Invoices = Sheet1.Range("D29").Value
in_TemplateSaved_Flag = Sheet1.Range("D32").Value
in_Template_Name = Sheet1.Range("D34").Value
in_File_Format = Sheet1.Range("D36").Value

'BasicInfo is null
'If in_payFrom = "" And in_payTo = "" And in_paymentType = "" And in_BIFileName = "" And in_amount = "" And in_localFolderPath = "" And in_CrCurrency = "" Then
' var_basicInfo_flag = "True"
'End If
'BasicInfo everything is filled
If in_payFrom <> "" And in_payTo <> "" And in_paymentType <> "" And in_BIFileName <> "" And in_amount <> "" And in_localFolderPath <> "" And in_CrCurrency <> "" Then
var_basicInfo_flag = "True"
End If

'FX is null
If in_FX_Date = "" And in_FX_Rate = "" And in_FX_Reference = "" And in_FX_Type = "" Then
var_FXInfo_flag = "True"
End If
'FX everything is filled
If in_FX_Date <> "" And in_FX_Rate <> "" And in_FX_Reference <> "" And in_FX_Type <> "" Then
var_FXInfo_flag = "True"
End If
'FX Type is Bank Rate or Assign later
If in_FX_Type = "Bank Rate" Or in_FX_Type = "Assign Later" Then
var_FXInfo_flag = "True"
End If

'Invoice is null
If in_Invoice_Type = "" And in_No_Of_Invoices = "" Then
var_InvoiceInfo_flag = "True"
End If
'Invoice everything is filled
If in_Invoice_Type <> "" And in_No_Of_Invoices <> "" Then
var_InvoiceInfo_flag = "True"
End If

'Set Output message
out_Optional_Message = ""
out_Mandatory_Message = ""
out_DuplicateTemplate_Message = ""

'Duplicate TemplateName check
If in_TemplateSaved_Flag = "Yes" Then
If in_Template_Name <> "" Then
lastrow = Sheets("SavedTemplates").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
For j = 2 To lastrow
tempString = Sheets("SavedTemplates").Cells(j, 15).Value
If UCase(Trim(in_Template_Name)) = UCase(Trim(tempString)) Then
var_Duplicate_TemplateName_flag = "True"
out_DuplicateTemplate_Message = "Please enter a unique template name."
Exit For
End If
Next j
Else
var_Duplicate_TemplateName_flag = "True"
out_DuplicateTemplate_Message = "Template name cannot be blank. Please enter a unique template name."
End If
End If

If var_basicInfo_flag = "False" Then
out_Mandatory_Message = out_Mandatory_Message + "Please ensure all information is added under Basic Info tab."
End If

If var_FXInfo_flag = "False" Then
out_Optional_Message = out_Optional_Message + "FX Info, "
End If
If var_InvoiceInfo_flag = "False" Then
out_Optional_Message = out_Optional_Message + "Invoice Info, "
End If
'Trailing comma removal
i = InStrRev(out_Optional_Message, ",")
If i > 0 Then
out_Optional_Message = Left(out_Optional_Message, i - 1) & Right(out_Optional_Message, Len(out_Optional_Message) - i)
Else ' comma not found in A1
out_Optional_Message = out_Optional_Message
End If
If var_FXInfo_flag = "False" Or var_InvoiceInfo_flag = "False" Then
If var_basicInfo_flag = "False" Then
out_Mandatory_Message = out_Mandatory_Message + vbNewLine
End If
out_Optional_Message = "Please ensure all details are entered or left blank under : " + out_Optional_Message + "tab." + vbNewLine
End If

'User alert
If var_basicInfo_flag = "False" Or var_FXInfo_flag = "False" Or var_InvoiceInfo_flag = "False" Or var_Duplicate_TemplateName_flag = "True" Then
MsgBox out_Mandatory_Message + out_Optional_Message + out_DuplicateTemplate_Message
Else 'Call other macro => File Creation
lastrow = Sheets("MasterList").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row + 1

'ActiveCell.FormulaR1C1 = "=Input!R[1]C[3]"
Sheets("MasterList").Cells(lastrow, 1).Value = Sheet1.Range("D3").Value ' Pay From
Sheets("MasterList").Cells(lastrow, 2).Value = Sheet1.Range("D5").Value ' Pay To
Sheets("MasterList").Cells(lastrow, 3).Value = Sheet1.Range("D7").Value ' Folder Path
Sheets("MasterList").Cells(lastrow, 4).Value = Sheet1.Range("D9").Value ' Payment Type
Sheets("MasterList").Cells(lastrow, 5).Value = Sheet1.Range("D11").Value ' Amount
Sheets("MasterList").Cells(lastrow, 6).Value = Sheet1.Range("D13").Value ' Credit Currency
Sheets("MasterList").Cells(lastrow, 7).Value = Sheet1.Range("D15").Value ' BI File Name
Sheets("MasterList").Cells(lastrow, 8).Value = Sheet1.Range("D18").Value ' FX Type
Sheets("MasterList").Cells(lastrow, 9).Value = Sheet1.Range("D20").Value ' FX Ref
Sheets("MasterList").Cells(lastrow, 10).Value = Sheet1.Range("D22").Value ' FX Date
Sheets("MasterList").Cells(lastrow, 11).Value = Sheet1.Range("D24").Value ' FX Rate
Sheets("MasterList").Cells(lastrow, 12).Value = Sheet1.Range("D27").Value ' Invoice Type
Sheets("MasterList").Cells(lastrow, 13).Value = Sheet1.Range("D29").Value ' Number of Invoices
Sheets("MasterList").Cells(lastrow, 14).Value = Sheet1.Range("D32").Value ' SaveTemplateFlag
Sheets("MasterList").Cells(lastrow, 15).Value = Sheet1.Range("D34").Value ' TemplateName
Sheets("MasterList").Cells(lastrow, 16).Value = Sheet1.Range("D36").Value ' FileFormat


Call createBIFile
End If

End Sub