Excel character limit macro with warning

phownz

New Member
Joined
Jul 28, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will the data to be checked always start at row 9?
 

RobVos

Board Regular
Joined
Feb 17, 2006
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option would be
VBA Code:
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
 
Last edited:

phownz

New Member
Joined
Jul 28, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top