|
OrderStream provides the ability to verify configurator property entries to alert the order entry user of data entry errors. The rules by which the alerts are generated are defined either in the product line Excel workbook or within a Macro contained within the workbook. The method used to define the alerts depends on the complexity of the rule being enforced by the custom check.
System Setup
Go to Application Preferences and view the Configurator Settings tab. Check the Enable Custom Checks options and click the Save button. This will activate the custom check functionality.
Sample Check 1
Creating a custom check using Sample Check 1 is effective in ensuring that a property has a value or notifying the user that an incorrect value has been entered, open the product line Excel workbook and then open the Visual Basic Editor (VB Editor) as shown below.
When the VB Editor opens, expand the Modules group to reveal Module1. Right click on Module1 and select View Code.
The Visual Basic code will be displayed on the right portion of the screen. Select the CustomChecks_SampleCheck1 function from the Procedures drop down as displayed below to be taken directly to the CustomChecks_SampleCheck1 function.
The CustomChecks_SampleCheck1 function contains some sample code to assist you in creating your own custom check. The sample code is as follows:
'sample check 1 - To use, rename procedure from CustomChecks_SampleCheck1 to CustomChecks
Public Sub CustomChecks_SampleCheck1()
With Sheets("Input")
'-------------------------
If CBool(.Range("C22")) = False Then
MsgBox "Display formula", vbSystemModal, "Custom Checks"
Exit Sub
End If
'-----------------------
'create your check here
'-----------------------
End With
End Sub
To use CustomChecks_SampleCheck1 you must rename the function to CustomChecks. Simply delete _SampleCheck1 from the function name and save.
Any text following an apostrophe is commented out, meaning that the code will not be executed. The following is a breakdown of what the code means.
With Sheets(“Input”) – the sheet in the product line Excel workbook the check is being performed in.
CBool – A Visual Basic function which coverts a value to a Boolean value (true or false).
.Range(“C22”) – The cell within the indicated spreadsheet the check is referring to. In this example the check is evaluating whether Input C22 is equal to False.
MsgBox – This will display the prompt to the user. The first value in quote is the message to display and the second value in quotes is the text which will appear as the prompt’s window title.
By using the above sample code as a guide, the following custom check was created.
Public Function CustomChecks() As String
With Sheets("Input")
'make sure width property contains a value
If CStr(.Range("C14")) = "" Then
MsgBox "You must enter a width to continue", vbSystemModal, "Unit Width"
Exit Sub
End If
'make sure width property is not too large
If CInt(.Range("C14")) > 84 Then
MsgBox "Unit is too wide. The maximum unit width is 84 inches", vbSystemModal, "Unit Width"
Exit Sub
End If
'make sure the height property contains a value
If CStr(.Range("C15")) = "" Then
MsgBox "You must enter a height to continue", vbSystemModal, "Unit Height"
Exit Sub
End If
End With
End Sub
Now that the custom check is in place, if the order entry user enters a width of 95 into the width property, the user will be prompted that the value is too large.
Sample Check 2
While the previous example can accommodate complex custom checks, a great deal of programming knowledge may be required to set them up. A more user friendly method of creating a more complicated custom check is to use the CustomChecks_SampleCheck2 function in the product line Excel workbook and to set up a sheet in the workbook which is structured to the requirements of the macro.
Open the product line Excel workbook and insert a new sheet called “Alerts” (if the Alerts sheet does not already exist). Structure the sheet as indicated in the example below.
Alert Header: The text which will appear in the window title of the displayed prompt.
Level: The degree of severity of the message. 1 = warning while 2 = critical
Display Alert: If value is TRUE, the alert message will displayed to the user in an alert message box.
Alert Message: The actual message which will appear in the body of the alert message box.
Create logic in the Alerts sheet which will monitor values in the Input sheet and when criteria which constitutes an error is present in the Input sheet, sets the Display Alert cell in the Alerts sheet for the relevant error message to TRUE.
Any number of error checks can be entered into the Alerts sheet. When the custom check macro runs, it will display the first error message where the Display Alert cell is set to TRUE. If there are multiple lines where the Display Alert cell is TRUE, only the first error prompt will be displayed. Once the user has corrected their data entry to correct the first error, if subsequent errors still exist, the first remaining error message will then be displayed. If you wish error messages to be displayed in a particular order, simply arrange the error message in a descending level of importance.
By default the CustomChecks_SampleCheck2 function is “commented out” which prevents the code from being run. To enable the custom macro, open the Visual Basic editor in the same method as described in Sample 1 and locate the code shown below. Note that you must rename the CustomChecks_SampleCheck2 function to CustomChecks to use the function.
'sample check 2 - To use, rename procedure from CustomChecks_SampleCheck2 to CustomChecks
Public Function CustomChecks_SampleCheck2() As String
Dim oSht As Worksheet
Dim sRow() As Variant
Dim nRow As Long
Dim nRowCount As Long
On Error Resume Next
Set oSht = ThisWorkbook.Sheets("Alerts")
sRow = oSht.Range("A2:D" & oSht.Range("A1").End(xlDown).Row).Value
nRow = 1
nRowCount = -1
nRowCount = UBound(sRow, 1)
For nRow = 1 To nRowCount
If IsEmpty(sRow(nRow, 3)) Then
'Do Nothing
ElseIf sRow(nRow, 3) = False Then
'Do Nothing
Else
CustomChecks = sRow(nRow, 4) & "|" & sRow(nRow, 2) & "|" & sRow(nRow, 1)
Exit For
End If
Next
Set oSht = Nothing
'Return message format: Message|Exclamation|Title
End Function
The only change to the macro you must make is to remove the apostrophes which appear at the start of each line of code and too rename the function to CustomChecks. Do NOT modify anything else.
Now when order entry personnel enter data in the configurator which matches the error logic defined in the Alerts sheet, the appropriate error message will be displayed from the configurator as shown below.
|
