Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

how to validate the last four digits ?

Copper Contributor

can anybody help me ?

I have my code here, but I get no response, if I type etc. 020202-1326

I need to validate the last 4 digits of this string xxxxxx-xxxx with hyphen between 6 and 7 digits.

I have a rule, that if the first 2 numbers in the last 4 digits are odd, then the last 2 numbers must also be odd, e.g. 070707-1337 is correct while 070707-1926 is incorrect.

Conversely, if the first 2 numbers in the last 4 digits are even, then the last 2 numbers must also be even, e.g. 070707-1426 is correct but 070707-1423 is incorrect

My code looks like this, but no msgbox popup ?

Dim from As Worksheet
Set frm = ThisWorkbook.Sheets("Enter data")

Dim strValue As String
strValue = CStr(frm.Range("I6").Text)
strValue = Replace(strValue, "-", "")

Dim firstPair As Integer
Dim secondPair As Integer


firstPair = CInt(Mid(strValue, 7, 2))
secondPair = CInt(Mid(strValue, 9, 2))

If (firstPair Mod 2 = 0 And secondPair Mod 2 <> 0) Or (firstPair Mod 2 <> 0 And secondPair Mod 2 = 0) Then
MsgBox "The last four digits of the number must be even or odd.", vbOKOnly + vbInformation, "number"
frm.Range("I6").Select
frm.Range("I6").Interior.Color = vbRed
frm.Range("I6").Font.Bold = True
frm.Range("I6").Font.Color = vbWhite
frm.Range("I6").Font.Name = "Arial"
frm.Range("I6").Value = "Enter a valid number"
Validate = False

Exit Function
Than If

2 Replies

@KewiDK 
It's weird to have the last line on the code of yours with Than if, that supposedly Then or End if...?

@KewiDK 
Apart from you last line of code, there is also some typos on some line,

  1. Corrected the variable name: You declared a variable from but used frm in your code. I corrected the declaration to Dim frm As Worksheet
  2. Removed the CStr function: The CStr function is not necessary when you’re getting the text from a range. So, I removed it and directly assigned the text to strValue.

Here’s the comparison for clarity:

Your code:

Dim from As Worksheet
Set frm = ThisWorkbook.Sheets("Enter data")

Dim strValue As String
strValue = CStr(frm.Range("I6").Text)
...
Than If

here's what I did:

Dim frm As Worksheet
Set frm = ThisWorkbook.Sheets("Enter data")

Dim strValue As String
strValue = frm.Range("I6").Text
...
End If