Data Validation (Limit on Length & Characters Must Be Uppercase)

1
votes

I have a column with data validation that currently limits the maximum number of characters a user can input to just 20. However, I would also like to include another limitation where the characters entered must be uppercase if they are alphabets. I'm assuming I would have to use a "Custom" data validation? If so, can anyone help me with the formula that would apply to the data in the entire column instead of just the selected cell(s)?

Lastly, I'm trying to change the error message that would display when the user does not meet the conditions defined, but I keep getting the default Microsoft Excel error message that pops up. How do I change the error message box to display a custom message of my choosing?

Dim shTemp As Worksheet: Set shTemp = ThisWorkbook.Sheets("Template")

With shTemp.Range("G2:G" & shTemp.Range("G" & Rows.Count).End(xlUp).Row).Validation
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, Operator:=xlLessEqual, Formula1:="20"
        .IgnoreBlank = True
        .InputTitle = "Check #"
        .ErrorTitle = "Check #"
        .InputMessage = "Enter 20 characters or less"
        .ErrorMessage = "You can only enter a maximum of 20 characters (uppercase) or less in this column!"
End With

Error Message Box

2
excelvba

2 Answers

0
votes

Yes, you are correct, you need to use a custiom validation. If you give it relative reference to the first cell in the range, Excel will adjust the references for the remaining cells.

The formula is =AND(LEN(<cell>)<=20,EXACT(<cell>,UPPER(<cell>)))

To make the messages work, add the .Show* = TRUE properties

Sub Demo()
    Dim shTemp As Worksheet
    Dim Rng As Range
    Dim Addr As String
    Set shTemp = ThisWorkbook.Sheets("Template")
    Set Rng = shTemp.Range("G2:G" & shTemp.Range("G" & shTemp.Rows.Count).End(xlUp).Row)
    Addr = Rng.Cells(1, 1).Address
    With Rng.Validation
        .Delete
        .Add Type:=xlValidateCustom, _
          AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, _
          Formula1:="=AND(LEN(" & Addr & ")<=20,EXACT(" & Addr & ",UPPER(" & Addr & ")))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Check #"
        .ErrorTitle = "Check #"
        .InputMessage = "Enter 20 characters or less"
        .ErrorMessage = "You can only enter a maximum of 20 characters (uppercase) or less in this column!"
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Note that the ... .End(xlUp).Row will apply the validation only to cells in column G down to the last cell with data. If you haven't already filled in some cells, you'll need anaother method to define the range, eg Set Rng = shTemp.Columns(7)

0
votes

@chris neilsen has given you the formula to use for your data validation rule. So that's great.

However, as you've noticed, the validation doesn't take place when pasting into the cell and, unless you paste as values, the validation rule gets deleted.

Therefore, I would suggest that you add VBA code to simply prevent pasting into any cell containing data validation. You can use the SelectionChange event to do so.

First, define a name for your range containing the data validation, let's say you call it DataValidationRange. Then, place the following code into the code module for your worksheet (right-click the sheet tab, and select View Code)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Not Intersect(Target, Range("DataValidationRange")) Is Nothing Then
        Application.CutCopyMode = False
    End If
        
End Sub