Userform textbox rules

[themightyreds]

I have a userform for the staff to capture cargo shipment details. Each shipment has a unique identifier called an Air Waybill (AWB). The AWB is made up of three parts

the Airline prefix (3 digits)
the serial number (7 digits)
the check digit (1 digit) - The check digit is derived by dividing the 7 digit Serial Number by 7. The remainder determines the Check Digit. Example: Serial Number 8114074 divided by 7 is 1159153 remainder 3. Therefore the Serial Number + Check Digit is 81140743

We are currently entering an AWB in the following format 236-81140743

I am having lots of issues of staff entering incorrect AWB details whether that be missing digits, incorrect format or incorrect check digit.

Apart from bashing them over the head each time they enter the details incorrectly can anyone offer me any solutions to highlighting the error at time of entry????

 

[RoryA]

Try this function:

Function IsValidWaybill(strInput As String) As Boolean
   Dim lngSerial As Long, lngCheck As Long
   IsValidWaybill = True
   If Not strInput Like "###-########" Then
      IsValidWaybill = False
   Else
      lngSerial = CLng(Left(Split(strInput, "-")(1), 7))
      lngCheck = CLng(Right(strInput, 1))
      If lngCheck <> lngSerial Mod 7 Then IsValidWaybill = False
   End If
End Function

You can use it in the Textbox_Exit event to validate the contents and then alert the user as required.

 

[themightyreds]

Try this function:

Function IsValidWaybill(strInput As String) As Boolean
   Dim lngSerial As Long, lngCheck As Long
   IsValidWaybill = True
   If Not strInput Like "###-########" Then
      IsValidWaybill = False
   Else
      lngSerial = CLng(Left(Split(strInput, "-")(1), 7))
      lngCheck = CLng(Right(strInput, 1))
      If lngCheck <> lngSerial Mod 7 Then IsValidWaybill = False
   End If
End Function

You can use it in the Textbox_Exit event to validate the contents and then alert the user as required.

Many thanks for your help but you are well beyond me! the textbox is "txtawb" I guess I add this ti the end of my script - what next!

 

[RoryA]

Assuming you have a button that saves the data to a worksheet, I would probably use two checks:
1. in the textbox exit event, check if it's valid, if not pop up a message and colour the textbox but allow the user to continue.
2. In the code for the button that saves the data, check the number again - if it's still invalid, pop up a message and refuse to save.

Here's a slightly more detailed version of the function. In a normal module, put this:

Option Explicit
Public Enum WayBillError
   wbeNoError = 0
   wbeInvalidFormat = 1
   wbeInvalidChecksum = 2
   wbeNoValue = 4
End Enum
Function GetWaybillError(strInput As String) As WayBillError
   Dim lngSerial As Long, lngCheck As Long
   GetWaybillError = wbeNoError
   If Len(strInput) > 0 Then
      If Not strInput Like "###-########" Then
         GetWaybillError = wbeInvalidFormat
      Else
         lngSerial = CLng(Left(Split(strInput, "-")(1), 7))
         lngCheck = CLng(Right(strInput, 1))
         If lngCheck <> lngSerial Mod 7 Then GetWaybillError = wbeInvalidChecksum
      End If
   Else
      GetWaybillError = wbeNoValue
   End If
End Function

then in the userform module you could have something like this:

Private Sub cmdSave_Click()
   Dim wbeError As WayBillError
   wbeError = GetWaybillError(txtAWB.Text)
   
   If wbeError = wbeNoError Then
      'save data
   ElseIf wbeError = wbeNoValue Then
      MsgBox "Way Bill Number is missing!"
      txtAWB.SetFocus
   Else
      MsgBox "Way bill number is still invalid - please check and correct it!"
      txtAWB.SetFocus
   End If
End Sub
Private Sub txtAWB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim wbeError As WayBillError
   wbeError = GetWaybillError(txtAWB.Text)
   If wbeError = wbeNoError Then
      txtAWB.BackColor = &H80000005
   ElseIf wbeError = wbeInvalidFormat Then
      MsgBox "Way bill number format is invalid - please check and correct it!"
      txtAWB.BackColor = 255
   ElseIf wbeError = wbeInvalidChecksum Then
      MsgBox "Way bill checksum digit is invalid - please check and correct it!"
      txtAWB.BackColor = 255
   End If
End Sub

 

[Norie]

Do you really need to have 1 textbox for the whole AWB on the userform?

Why not 1 textbox (or even a combobox) for the Airline prefix and one for the Serial number?

 

[themightyreds]

Do you really need to have 1 textbox for the whole AWB on the userform?

Why not 1 textbox (or even a combobox) for the Airline prefix and one for the Serial number?

I could potentially do that they can be concatenated into the same cell afterwards...whats the benefit?

 

[Norie]

Well I was just assuming that there would be a list airlines which could be picked from.

That part could be easily validated, leaving just the serial number needing to be validated via code.

In fact you could disable the serial no textbox until a valid airline is selected.

 

[themightyreds]

Yes I can see that by breaking it down it begins to eliminate areas of error, I suspect that the majority of the issues are arising from the last eight digits though almost all entries begin with the same prefix.

 

[Norie]

Why not eliminate the need for them to enter the check digit in the first place?

Looks like a pretty simple calculation, so once they've entered the original serial number just have code calculate the check digit and append it.

Or am I missing something?

 

[themightyreds]

um no I guess you are not missing anything. I am just guilty of living in my little bubble and not questioning our processes or actions!

Can you help me with the code :0)