VBA Macro to Count Characters in a cell

[Larcen60]

Hello!

I'm trying to create a macro that gives a message box and fills a cell red when a user enters over 15 characters in a cell. However, when I create the macro I notice that it only runs if you click back into the cell a second time. Is there any way to make the macro run upon exiting the cell the first time? My concern is that a user won't click back into the cell once they've already entered too many characters. Also, is there a way to make the red fill go away upon the user correcting the cell to 15 characters or less?

Thanks very much in advance!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
     If Len(Target) > 15 Then
     MsgBox "Text is over 15 characters."
     Range("A1").Interior.Color = RGB(255, 0, 0)
          End If
End If
End Sub

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Len(Range("A1")) > 15 Then
        MsgBox "Text is over 15 characters."
        Range("A1").Interior.Color = RGB(255, 0, 0)
        Range("A1").Select
    Else
        Range("A1").Interior.Color = xlNone
    End If
End If
End Sub

 

[Peter_SSs]

Any reason to use a macro instead of Excel's standard Data Validation (on the Data Ribbon tab) with the option Allow: Text length?

 

[Larcen60]

That's perfect, thanks so much! Worked great!

Peter, I didn't use Data Validation because I was hoping to get a cell fill in there as well. I don't think Data Validation will allow a cell fill, although I might be wrong!

Thanks again for the help!

 

[jproffer]

Conditional Formatting will do it too...or maybe along with validation...

Use formula as rule (or however it words it )

=LEN(A1)>15

 

[Peter_SSs]

Peter, I didn't use Data Validation because I was hoping to get a cell fill in there as well. I don't think Data Validation will allow a cell fill,

You are right that DV does not offer that feature. However, with DV the feature should not be needed as the the DV can stop the user entering more than 15 characters in the first place!