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!

 

[ez08mba]

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.

In some cases, yes, some will be exact. For example:
Range("G3") : FA[A-Z][A-Z]-##-####

Others may be custom number formats applied to cells that will need to be validated by the function created in the end. For example:
Range("Z9") : _($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)

Others will be using standard formatting options like "General", "Short Date", or particular decimal places (e.g. 00.00%, $##,##0.00)

Though we are attempting to apply formatting to the cells to control the data formatted (e.g. short date), we don't want to assume the data is correct. So we will be evaluating for each and alert the user to correct it if needed.So even for the simplest data entry, we will still be checking.

 

[Akuini]

It isn't clear to me, are you going to use Like operator or regex to validate the cells value? or both?
Your example doesn't shows any regex pattern.

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

What exactly do you have in the table? pattern for regex? or string for cell format? or both?
And in another column do you have the range address that will be validated by each pattern/cell format?

Could you upload a sample workbook to a free site such as dropbox.com & then put the link here?
It's easier to understand the problem by reading a sample file.

 

[Peter_SSs]

In some cases, yes, some will be exact. For example:
Range("G3") : FA[A-Z][A-Z]-##-####

That seems to indicate that the answer to my previous question is that those @ symbols were representing upper-case letters only, not just any text value.
If that is the case, then that particular pattern should be able to be validated with Excel's Data Validation feature as shown below.

Of course you need to keep in mind that
- Data Validation can be defeated if the user pastes into a DV cell/range from another cell/range that has no DV or different DV.
- VBA code can be defeated if the user does not enable macros, and also possibly if they change the location of the cell(s) in question. For example by inserting/deleting rows/columns or dragging the cell(s) to another location.
Usually a matter of assessing who will be using the sheet to decide the safest course of action.

Excel Workbook
	G
3	FAQX-38-7865
4
DV

#VALUE!

 

[ez08mba]

Peter and Akuini!

Akuini - Sorry, I didn't intend for this to be difficult. I'm simply asking for insight to the best option, when using the customers workbook template with cell data formatting, how to restrict a user to a particular format. There isn't anything I can provide you directly at the moment due to a Non-Disclosure Agreement. There is nothing presently in place such as a RegExp piece, Like statements; however, I provided something below and wonder what the opinion might be.

Peter - I think you are on track and you make some good points. I think the plan is for us to take this workbook template and lock it down so that only data entry cells can be manipulated. I believe that will control some of the points you made. Your example is good. This workbook template will need to be edit able by the customer's developers. So I'm wondering if we incorporate a hidden Worksheet that acts as a cell formatting table, in combination with a Worksheet function such as "Private Sub Worksheet_SelectionChange(ByVal Target As Range)", that we can control the formatting and make updates easily update-able?

For example, I have a "Formatting" worksheet that acts as a RegExp table for specific cells then compares.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Selection.Count = 1 Then


        Dim addr As String: addr = Target.Address
        Dim i As Integer: i = 0
        
        On Error Resume Next
         i = Sheets("Formatting").Range("A:A").Find(addr).Row
        On Error GoTo 0
        
        If i > 0 Then
            If IsEmpty(Range(addr)) = False Then
                
                Dim regX As New regexp
                regX.Pattern = Sheets("Formatting").Cells(i, 2).Value
                regX.ignorecase = False
                
                If regX.test(Range(addr).Value) Then
                    MsgBox "Match"
                Else
                    MsgBox "No Match" & vbCrLf & "RegExp: " & Sheets("Formatting").Cells(i, 2).Value
                End If
                
            End If
        End If
        
    End If
    
End Sub

"Formatting" Worksheet

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]$A$2[/TD]
[TD]^FA[A-Z][A-Z]-\d{1,2}-\d{1,4}$[/TD]
[/TR]
[TR]
[TD]$D$3[/TD]
[TD]^[abcxyz]$[/TD]
[/TR]
[TR]
[TD]$D$4[/TD]
[TD]^[A-Z]{1}[\d]{6}$[/TD]
[/TR]
[TR]
[TD]$G$9[/TD]
[TD]^A{1,3}$[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Your idea of using regX codes in a Formatting sheet could work I think. More in a moment.
Also, To be more robust, your code should try to deal with situations where the user enters more than one value at a time (with Ctrl+Enter, Copy/Paste etc)

I think your use of Selection_Change will not be robust. You are trying to prevent invalid entries. You can't check the entry until the entry has been made and when entering a value it can be confirmed by, for example
- Enter, which normally then selects the cell below so your code would be checking the wrong cell
- Tab, which normally then selects the cell to the right so your code would be checking the wrong cell
- Clicking anywhere with the mouse which means your code would almost certainly be checking the wrong cell
- Pasting. If multiple cells are pasted at once your code will not check any of them.

With the Formatting sheet, if it is feasible (size of work area?), I would put the regX patterns actually in the corresponding cells. So, for your example, I would set up the Formatting sheet like this.

Excel Workbook
	A	B	C	D	E	F	G
1
2	^FA[A-Z][A-Z]-\d{1,2}-\d{1,4}$
3				^[abcxyz]$
4				^[A-Z]{1}[\d]{6}$
5
6
7
8
9							^A{1,3}$
Formatting

Then I would try this Worksheet_Change code in your entry sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  Static RX As Object
  
  Dim wsF As Worksheet
  Dim c As Range
  Dim sMsg As String
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  Set wsF = Sheets("Formatting")
  Application.EnableEvents = False
  For Each c In Target
    If Len(c.Value) Then
      If Len(wsF.Range(c.Address).Value) Then
        RX.Pattern = wsF.Range(c.Address).Value
        If Not RX.Test(c.Value) Then
          sMsg = sMsg & vbLf & c.Address(0, 0) & vbTab & c.Value & vbTab & RX.Pattern
          c.ClearContents
        End If
      End If
    End If
  Next c
  Application.EnableEvents = True
  If Len(sMsg) Then MsgBox "The following invalid enties have been cleared." & vbLf & vbLf & "Cell" & vbTab & "Entry" & vbTab & "Valid Pattern" & sMsg
End Sub

To see how it works with multiple entries, put XXX in D1 and YYY in D2 then copy/paste those 2 entries together into D3:D4 of your entry sheet.


Finally a couple of comments about your regX patterns
- The {1} in the D4 pattern is superfluous, as are the [] around the \d
- I think it a bit safer to 'escape' a - in a pattern with \- since the - character can have a non-literal meaning as well (eg [A-Z])

 

[ez08mba]

Hi Peter!

I agree, there are some limitations. I like this idea and it seems to be working. As for my RegExp patterns, I don't do them enough so I have to relearn them every time, LOL! I definitely want to avoid redundancy to eliminate confusion.

Thanks Peter and Akuini with your time and effort. I now have a new built-in issue with the Excel .Find, but that is a separate post and there may be a workaround.

Thanks again!

 

[Peter_SSs]

Hi Peter!

.. it seems to be working.

Thanks again!

Good news!
You're welcome.