Limit character type and length in cell possible?

One approach, in the sheet module, will activate when any cell is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target Like "[A-Za-z]##[A-Za-z]####" Then
    MsgBox "Wrong"
    Application.Goto Target
    Application.EnableEvents = False
    Target.Clear
    Application.EnableEvents = True
End If

End Sub

open a new workbook and expriment this

rightclick sheet tab and click view code there you copy this evnet code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo eerr
If Len(Target) = 8 And Not IsNumeric(Left(Target, 1)) _
And IsNumeric(Mid(Target, 2, 2)) And Not IsNumeric(Mid(Target, 4, 1)) _
And IsNumeric(Right(Target, 4)) Then
 MsgBox "valid"
 Else
  MsgBox "not valid"
  Target.Clear
 End If
eerr:
Application.EnableEvents = True
End Sub

the only problem when you enter something in any cell this event code runs. to restrict this only some range or column you can add a condition in the beginning.

your comments please

Tony - if you add this line before the current If, it will only trigger when a cell in A1:A10 is changed.

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub

Thanks for the amazing help all of you.

Venkat, your code does exactly as what StephenR's does but added 'valid' messagebox (which isn't needed really, it's either going to be not valid or valid) and Stpehen's code is a bit more compact. Thanks a lot for the help though, it should come in handy some other time!

StephenR, cheers for the extra line there, I was looking at previous workbooks I've created that had that exact line in it but again, you're quicker! I knew it had something to do with the 'Intersect' bit but wasn't sure what the rest of it should look like, I am still very much a VBA nubbin

Thanks a lot for all the help and hints everyone!

Tony

I think I may have been a tad hasty in tagging this 'Solved'

I'm trying to have the above code accommodate another column with a different format e.g: '12A/12345678' but I can't seem to get it to work.

I thought about setting the ranges with names and the respective format they should be in but then it stops everything from working

As mentioned I am VBA nubbin but not scared to learn!

Cheers,
Tony

Just checking to see if anyone has an answer to the above

Thanks,

Hi Romper,

Ok, I probably didn't think about this enough before I posted the original question - too eager!
Full explanation below:

I've got 7 columns, A:I.

Columns B and C are freeform, anything goes into these babies
Column F is a date field (simple enough)
Columns A,C,E and G are where all the action's taking place

Columns A and G needs to be like 'THSF12345' where the 'THSF' part never changes (only difference in the 2 columns is that in G 'it's 'THFB12345'
Column C needs to be like 'A11B1234' where both letter and numbers will change (answered originally in this thread)
Column E needs to be like '11A/12345678' where both numbers and the letter will change and the important part is the '/', this doesn't change.

I tried to do it on my own and of course getting nowhere!

I appreciate that my original post did not encompass everything I wanted so apologies.....I wanted to give it a try on my own first before asking for the rest.

Thanks again

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target Like "##[A-Za-z]/######" Then
        Application.Goto Target
        Application.EnableEvents = False
        Target.Clear
        Application.EnableEvents = True
    End If
End Sub

Originally Posted by StephenR

Scratch this. I can't read.

Or is it I who cannot explain in plain English???

SNB, what's the point in posting the exact same code as in previous posts? Thanks for the help anyway

Or is it I who cannot explain in plain English???

No, it's me who can't read English (let alone write French).

Hi Romper,

I pasted the code in and it works but it fires in columns B,D and F as well.
I only want it to look at columns A, C, E, G with the criteria mentioned in my earlier post.

Any suggestions?

Thanks,
Tony

HAH! Nice spot Romper, ok so it's not EXACTLY the same.....my mistake....missed out the messagebox bit
But, otherwise....c'mon?

StephenR, I only know "Stewardess!!! Quest qui ces't??!!" (sorry, a Lee Evans thing lol)

YES!!!!!!!!!!!!!!!!!!!!!!
Cool, thanks for all the help guys.

Just a few points:
Romper, what the hell is "hair code"? (some VBA term I'm not familiar with?)
SNB, profound apologies

Cheers,
Tony

Just for polishing:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A:G")) Is Nothing Then
        If Target.Column Mod 2 = 0 Then Exit Sub
        If Not Target Like Split("THSF#####|[A-Za-z]##[A-Za-z]####|##[A-Za-z]/########|THFB#####", "|")(Target.Column \ 2) Then
            MsgBox "Invalid value"
            Application.Goto Target
            Application.EnableEvents = False
            Target.Clear
            Application.EnableEvents = True
        End If
    End If
End Sub

PS. This is definitely not exactly the same code (although the performance is)

snb: very neat.

SNB, where in your code does it state on what columns to run the code?
I could see where it was going wrong in Romper's version before the change in the range was put in but not too sure where in yours it differentiates what columns to look at?

Please excuse the questions, I just want to understand what each part does so that it will help me get a grip on VBA.

It takes two steps to tango...

First is the target in columns A...G ?

   If Not Intersect(Target, Range("A:G")) Is Nothing Then

Second: is the columnumber odd or even ?
If it's even then skip the remaining code.

   If Target.Column Mod 2 = 0 Then Exit Sub

How clever! Didn't even think for a second there that the columns I needed to look at coincided with odd numbers!

Well done m8 and cheers for the help.

I think that it's time now to mark this thread solved lol

Tony

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column<8 and  Target.Column Mod 2 = 1 Then
        If Not Target Like Split("THSF#####|[A-Za-z]##[A-Za-z]####|##[A-Za-z]/########|THFB#####", "|")(Target.Column \ 2) Then
            MsgBox "Invalid value"
            Application.Goto Target
            Application.EnableEvents = False
            Target.Clear
            Application.EnableEvents = True
        End If
    End If
End Sub