Excel character limit macro with warning

[phownz]

Hi all,

I'm somewhat new to macros in Excel and I've been experimenting with a couple of macros but can't quite get what I need. I have three columns (AF, AG, AH) in an Excel sheet that I need to limit with a certain amount of characters (I can't use formulas as the data will be copy pasted) The required character limitations are below;

Column AF - 200 character limit
Column AH - 350 character limit
Column AG - 500 character limit

I was hoping to get an error message for each column that warns the use the character limit has been exceeded but does not truncate the value. I was also looking into trying to get the cell to be highlighted in a different colour but I cannot get my version to work. I've pasted it below but of course it doesn't need to follow this format if you feel there is a better way. It's possible I am missing something obvious as I'm new to macros; this example was only for one column AF and the cell range 9 and 10 just to test but the plan would be to apply it to the three columns (I assume individually would be better) and for say the first 500 cells as a default.

Sub CharacterLimit()
Dim txt As String
Dim r As Range, rng As Range
Set rng = Range("AF9:AF10")
For Each r In rng
    If Len(r.Text) > 200 Then
        r.Interior.Color = 65535
        txt = txt & vbCrLf & r.Address(0, 0)
    End If
Next r
MsgBox "Cell(s) that exceeded character limits: " & vbCrLf & txt
End Sub

Any help would be appreciated, or if you need anything further please let me know.

 

[Fluff]

Hi & welcome to MrExcel.
Will the data to be checked always start at row 9?

 

[RobVos]

Your code will work, but you are setting the range only to AF9:AF10 = two cells. If you want the entire column, either use a dynamic range or use "AF:AF" for it (this would slow the code - so better to define the range).

You might want to use conditional formatting to change the color, so it will change back once corrected.

 

[Crystalyzer]

I have created two macros as follows:

This one should be placed in a module in the workbook vbe and can be run to check ALL cells in the used range in columns AF:AH

Sub CharacterLimit()
    Dim txt As String
    Dim lim As Long
    Dim rng As Range, c As Range
    Dim lRow As Long
    
    lRow = Cells(Rows.Count, 32).End(xlUp).Row
    
    Set rng = Range(Cells(1, 32), Cells(lRow, 34))
    
    For Each c In rng
        txt = ""
        Select Case c.Column
        Case 32
            lim = 200
            If Len(c.Value) > lim Then
                c.Interior.Color = 65535
                txt = txt & vbCrLf & c.Address(0, 0)
            End If
        
        Case 33
            lim = 350
            If Len(c.Value) > lim Then
                c.Interior.Color = 65535
                txt = txt & vbCrLf & c.Address(0, 0)
            End If
        
        Case 34
            lim = 500
             If Len(c.Value) > lim Then
                c.Interior.Color = 65535
                txt = txt & vbCrLf & c.Address(0, 0)
            End If
        End Select
        
        If txt <> "" Then MsgBox "Cell exceeds character limit of " & CStr(lim) & vbCrLf & txt
    Next c
    
End Sub

this one should be placed in the worksheet section of the vbe and will run anytime the worksheet is changed and notify the user he has entered a value that exceeded the character limis

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column >= 32 And Target.Column <= 34 Then
        Dim txt As String
        Dim lim As Long
        
        txt = ""
        
        Select Case Target.Column
        Case 32
            lim = 200
            If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
                                
            End If
        
        Case 33
            lim = 350
            If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
            End If
        
        Case 34
            lim = 500
             If Len(Target.Value) > lim Then
                Target.Interior.Color = 65535
                txt = txt & vbCrLf & Target.Address(0, 0)
            End If
        End Select
        
        If txt <> "" Then MsgBox "Cell exceeds character limit of " & CStr(lim) & vbCrLf & txt
    End If
End Sub

 

[Fluff]

Another option would be

Sub CharacterLimit()
Dim Txt As Variant, Ary As Variant
Dim i As Long

Ary = Array(200, 500, 350)
For i = 0 To UBound(Ary)
   With Range("AF2", Range("AF" & Rows.Count).End(xlUp)).Offset(, i)
      Txt = Filter(Evaluate("transpose(if(len(" & .Address & ")>" & Ary(i) & ",row(" & .Address & ")-min(row(" & .Address & "))+1, ""X""))"), "X", False)
      If UBound(Txt) >= 0 Then MsgBox "Col " & Split(.Address(1, 0), "$")(0) & " row(s) that exceeded character limits: " & vbCrLf & Join(Txt, vbLf)
   End With
   Erase Txt
Next i
End Sub

You can then use conditional formatting to highlight those cells that exceed the character limit

 

[phownz]

I honestly do not know how you are able to put these together so quickly... But I really appreciate the effort. It's also interesting to see the different approaches to tackling the problem, almost more helpful than the solution itself. I tested both and they both work as expected. Thank you so much!

I would have struggled with the column duplication I think. My approach was going to be a macro per column (purely down to my knowledge with Excel macros) This is honestly perfect.

 

[Fluff]

Glad we could help & thanks for the feedback.