exact sequence in certain cells?

[AwesomeSteph]

I am looking for a VBA to only allow an exact sequence for instance if cells in column A aren't typed in containing "A-12-34-5678-01AAA-123A-B" with same number of numbers between dashes then they get an error and it is deleted. Any suggestions?

 

[AwesomeSteph]

I was thinking something like this

Sub ValidatePattern()


    Dim a As Range, r As Range
    Set a = [G5:G6969]
    If Intersect(Target, a) Is Nothing Then Exit Sub
    For Each r In Target
        If Not r.Value = "##-##-####-##[A-Z][A-Z][A-Z]-###[A-Z]-[A-Z]" Then
            r.ClearContents
        End If
    Next r
Exit Sub


End Sub

but it isn't working.

 

[dreid1011]

Perhaps something like this:

Sub CheckPattern()
Dim sMatch As Boolean

.....
sMatch = r.Value Like "##-##-####-##[A-Z][A-Z][A-Z]-###[A-Z]-[A-Z]"
If Not sMatch Then
r.ClearContents
End If
.....

End Sub

 

[AwesomeSteph]

I'm not as fluent in VBA as I would like to be, where is that code distinguishing the range?

 

[dreid1011]

It is not. I apologize, I typed in the "...." to indicate the rest of your code as you posted in #2 so you could take what I provided and put it in with yours. Try this:

Sub ValidatePattern()


    Dim a As Range, r As Range
    Dim sMatch As Boolean
    Set a = [G5:G6969]
    If Intersect(Target, a) Is Nothing Then Exit Sub
    For Each r In Target
    sMatch = r.Value Like "##-##-####-##[A-Z][A-Z][A-Z]-###[A-Z]-[A-Z]"
    If Not sMatch Then r.ClearContents
    Next r
Exit Sub


End Sub

 

[AwesomeSteph]

Oh ok, I'm sorry I'm not quite seasoned and trying to learn as I go. Thank you for your help! This isn't working though, should this not be in the objects? Do I have to create a module for this to work?

 

[dreid1011]

Oh ok, I'm sorry I'm not quite seasoned and trying to learn as I go. Thank you for your help! This isn't working though, should this not be in the objects? Do I have to create a module for this to work?

Ok, I see what we need. No, you don't need a module, this can go in the worksheet object. However, there are some inconsistencies with your code and how you are trying to use it. Instead of a Sub, it looks like you want a worksheet_change event. So we'll use the following code. Also, I am not sure your setting of "a" was correct.

Try this:

Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range
Dim sMatch As Boolean
Set a = Range("G5:G6969")
Application.EnableEvents = False
If Not Intersect(Target, a) Is Nothing Then
    sMatch = Target.Value Like "##-##-####-##[A-Z][A-Z][A-Z]-###[A-Z]-[A-Z]"
    If Not sMatch Then Target.ClearContents
End If
Application.EnableEvents = True
End Sub

 

[AwesomeSteph]

Thank you so much!! I was going through old code I've used and did figure out it needed to be a worksheet_change. thank you again for all your help it works now

 

[dreid1011]

You're welcome. Thanks for the feedback.

 

[AwesomeSteph]

I have another question about this code that I'm using now, Is there a way add to this code to make it so that the users can not use the "paste" feature? I do not want the users to be copying from a different sheet to paste to this column. The sheet they want to copy from has a formula and when it is put in this column it stops the macro from working.