A validation need --- Excel VBA (Macros)

*Always first two characters should be "V" and "-".
*third character should be alphabetic.
*last four characters should be numeric only.
*The length should be seven.

I need a validation like below example.

e.g. V-B0004

Thank you very much guys

This formula gives you True if the 4 rules are true and False otherwise

Formula:

=IFERROR(AND(LEFT(A2,2)="V-",CODE(MID(A2,3,1))>=65,CODE(MID(A2,3,1))<=90,ISNUMBER(0+RIGHT(A2,4)),LEN(A2)=7),FALSE)

If you want validate the use of lowercase=uppercase you can use UPPER(A2) instead A2

Originally Posted by José Augusto

This formula gives you True if the 4 rules are true and False otherwise

Formula:

=IFERROR(AND(LEFT(A2,2)="V-",CODE(MID(A2,3,1))>=65,CODE(MID(A2,3,1))<=90,ISNUMBER(0+RIGHT(A2,4)),LEN(A2)=7),FALSE)

If you want validate the use of lowercase=uppercase you can use UPPER(A2) instead A2

Thank you for the reply.But I need this from VBA frnd

As you posted this in the excel macro section I'll post a macro solution as well

Option Explicit

Sub Cheker()
Dim cell As Range

For Each cell In Range("A2:A6")

 If Mid(cell, 1, 2) = "V-" And Mid(cell, 3, 1) Like "[A-Za-z)]" And IsNumeric(Mid(cell, 4, 4)) And Mid(cell, 4, 4) > 0 And Len(cell) = 7 Then
  cell.Interior.Color = vbRed
 End If
 
 Next

End Sub

Alf

If you do not mind attach the VBA file frnd pls

Originally Posted by ThiliSuper

If you do not mind attach the VBA file frnd pls

Originally Posted by Alf

As you posted this in the excel macro section I'll post a macro solution as well

Option Explicit

Sub Cheker()
Dim cell As Range

For Each cell In Range("A2:A6")

 If Mid(cell, 1, 2) = "V-" And Mid(cell, 3, 1) Like "[A-Za-z)]" And IsNumeric(Mid(cell, 4, 4)) And Mid(cell, 4, 4) > 0 And Len(cell) = 7 Then
  cell.Interior.Color = vbRed
 End If
 
 Next

End Sub

Alf

If you do not mind attach the excel VBA file frnd pls

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, msg As String
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In Intersect(Target, Columns(1))
        If r.Value <> "" Then
            If Not r.Value Like "V-[A-Za-z]####" Then
                MsgBox "Invalid Entry", vbCritical, r.Value
                r.ClearContents
            End If
        End If
    Next
    Application.EnableEvents = True
    If Len(msg) Then MsgBox msg, , "Invalid entry"
End Sub

I guess you can't apply the code, so attach a workbook showing where(range) to apply.

Originally Posted by jindon

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, msg As String
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In Intersect(Target, Columns(1))
        If r.Value <> "" Then
            If Not r.Value Like "V-[A-Za-z]####" Then
                MsgBox "Invalid Entry", vbCritical, r.Value
                r.ClearContents
            End If
        End If
    Next
    Application.EnableEvents = True
    If Len(msg) Then MsgBox msg, , "Invalid entry"
End Sub

I guess you can't apply the code, so attach a workbook showing where(range) to apply.

This is very useful frnd. But The cell clears whole code when the user enters wrong code.How do I validate one by one with different validations ?

Originally Posted by ThiliSuper

How do I validate one by one with different validations ?

No idea about what you are trying to say.

What exactly are you trying to do?

Originally Posted by jindon

No idea about what you are trying to say.

What exactly are you trying to do?

eg : V-B0004

When user enters first character as alphabetic(except capital V) or numeric value or special character -- Validation message should be "First character should be V"
When user enters second character as special character(except -) or numeric value or alphabetic -- Validation message should be "second character should be -"
When user enters third character as special character or numeric value-- Validation message should be "third character should be alphabetic"
When user enters last four characters as special character or alphabetic -- Validation message should be "last four characters should be numeric values"