VBA: Is there a way to validate a cell's value against the same cell's formatting?

[ez08mba]

Is there a way to validate a cell's value against the same cell's formatting?

What we are trying to do...

I see there is the "match" function but I don't think this is what the customer is looking to do. I believe this is just looking for a value that matches a designated value.

When the customer (user) enters a value into a cell is there a way to validate the value against the formatting through VBA? Or is there a control for that built-in to Excel? Here are a couple examples:

Example 1: A cell has a "Short Date" format but the user enters "November" or maybe something totally different "Bob"

Example 2: The customer would like a custom format (e.g. FA@@-##-####) forcing them to FACD-12-3456 and reject FA12-AC-3456.

Any suggestions. Could this be done checking with Excel VBA? Could it be a triggered by the cell format... as in it won't accept the value?

Thanks!

 

[shknbk2]

Here's how I would do it. Note, however, that there are probably much better ways, and maybe we'll discover them if others join in.

You can use Data Validation that is built into Excel. Select the cell you want to have validated and select the Data Validation option in the Data tab.

For Example 1: In the Allow field of the DV wizard, select Date. For the Data field, choose "greater than". In Start Date, choose a date that is well before any logical date that would be used in the cell (e.g., 1/1/1900). Any use of text like "bob" or "November" will trigger the validation warnings.

By the way, also in the DV wizard, you'll want to pay attention to the "Input Message" and "Error Alert" tabs to see if you want to use them and set them up if you do.

For Example 2: I first built a formula in an adjacent cell to test whether the format was entered correctly in A1. This is what I came up with:
=AND(LEN(A1)=12,MID(A1,1,2)="FA",ISERROR(VALUE(MID(A1,3,1))),ISERROR(VALUE(MID(A1,4,1))),MID(A1,5,1)="-",ISNUMBER(VALUE(MID(A1,6,2))),MID(A1,8,1)="-",ISNUMBER(VALUE(MID(A1,9,4))))
Now, this formula can be copied and pasted into the DV wizard by using the "Custom" entry in the Allow field and pasting the formula in the "Formula" field. You can set up an Error Alert that says something like "The format of this cell must be: FA@@-##-####" if the entry is wrong.

 

[ez08mba]

Hey shknbk2!

Interesting! I didn't think about using Data Validation. That seems like one good viable option.

I was also searching on the Internet and came across where I might be able to use a "Worksheet_SelectionChange" function with a "Range" parameter contained within the worksheet's associated VBA code view. Then I thought I may be able to use this function with a table of Regular Expressions associated to particular cell ranges, which could evaluate the data on a selection change.

Just throwing that out there too. I'm for simplest but yet most logically efficient option. I'm curious what others may suggest as well.

Thanks for the ideas and input. They are definitely worth considering.

 

[Akuini]

Using vba, try this:

This is an Event Procedure, you need to put it in the code module of the sheet (say sheet1).
So copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

You may need to amend the code in this part:
Range("A1:A10")
Like "[A-Z]-##"
MsgBox "Use this pattern: .... "

I think the pattern should be: FA[A-Z][A-Z]-##-####

Dim rhA As Range
Dim xFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo skip:
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target,[COLOR=#0000ff] Range("A1:A10")[/COLOR]) Is Nothing Then
        If Not Target.Value [COLOR=#0000ff]Like "[A-Z]-##" [/COLOR]And Len(Target) > 0 Then
            Application.EnableEvents = False
            Target.Activate
            Application.EnableEvents = True
            xFlag = True
            Set rhA = Target
        Else
            xFlag = False
            Set rhA = Nothing
        End If
    End If

Exit Sub
skip:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo skip:

If Target.Cells.CountLarge <> 1 Then Exit Sub
     If xFlag = True And Not rhA Is Nothing Then
            Application.EnableEvents = False
            rhA.Activate
            [COLOR=#0000ff]MsgBox "Use this pattern: ..."[/COLOR]
            Application.EnableEvents = True
     End If

Exit Sub
skip:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True

End Sub

 

[Akuini]

I think the code in post 4 can be shorter:

Dim rhA As Range
Dim xFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, [COLOR=#0000cd]Range("A1:A10")[/COLOR]) Is Nothing Then
        If Not Target.Value[COLOR=#0000cd] Like "[a-z]-##"[/COLOR] And Len(Target) > 0 Then
            xFlag = True
            Set rhA = Target
        Else
            xFlag = False
            Set rhA = Nothing
        End If
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo skip:

If Target.Cells.CountLarge <> 1 Then Exit Sub
     If xFlag = True And Not rhA Is Nothing Then
            Application.EnableEvents = False
            rhA.Activate
            [COLOR=#0000cd]MsgBox "Use this pattern: ..."[/COLOR]
            Application.EnableEvents = True
     End If

Exit Sub
skip:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True

End Sub

 

[Peter_SSs]

Example 2: The customer would like a custom format (e.g. FA@@-##-####) forcing them to FACD-12-3456 and reject FA12-AC-3456.

In this example, what exactly do the "@" symbols represent?
- Any text character (Ky^%5$! etc)?
- Any non-digit character (same as above excluding the 5)?
- Any letter A-Z (capitals only?)?
- Something else?

 

[ez08mba]

In this example, what exactly do the "@" symbols represent?

https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/

This wasn't the only source, but "@" represents a Text Placeholder when creating a custom format. You can see it in some of the format options in Excel. However, I noticed when trying to create a custom format with the character it was rejected by Excel.

 

[ez08mba]

If Not Target.Value Like "[a-z]-##" And Len(Target) > 0 Then

Hi Akuini!

So are you thinking I might substitute after the "Like" statement a regular expression string referenced from a data table (e.g. separate worksheet)? Just asking since each particular field will probably have a different format requirement, and there may be 100 fields to compare -- some with similar formatting requirements and others different.

 

[Peter_SSs]

This wasn't the only source, but "@" represents a Text Placeholder when creating a custom format. You can see it in some of the format options in Excel. However, I noticed when trying to create a custom format with the character it was rejected by Excel.

Yes, I'm aware of the use of @ in custom cell formatting but what I was trying to determine was exactly what you want allowed and/or disallowed in positions 3 & 4 of your cell input. You gave one single example where those characters were both upper case letters so I don't know what, if any, variety you want allowed in those 2 positions.

For example, is this allowed? FA---56-9876

 

[Akuini]

So are you thinking I might substitute after the "Like" statement a regular expression string referenced from a data table (e.g. separate worksheet)?

No, Like operator is different from regular expression. We will need another code to use regular expression.
Can you show us some example of the patterns/format requirements?
And are you gonna use these patterns to validate cells value as full-match or partial-match?
I mean for example, if the regex pattern is [a-z]{2} then for full match "aa" is true but "aaa" is false, while for partial-match both are true.