EXCEL VBA Count Characters in each cell

I want to coun the characters in each cell in column d.  then check to make sure that the character is not greater than 4 if it is msg box sorry cannot be greater tha for charaters.

any suggestions
RickstarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bruintjeCommented:
if you solve this at the input side you don't have to check after the fact

set a validation for the cells in the d column
-first select the column
-then choose data | validation | settings | allow textlength | min = 0 and max = 4
-on the input message tab put your message to the user

hope this helps

RickstarAuthor Commented:
True, infact that is what I am currently using but my when you copy and paste into column D the validation is destroyed.
bruintjeCommented:
pity that is the nicer solution but

you could give this a try

paste it all in the sheet object of the sheet you want to do this for

Option Explicit
'global vars
Dim rngTarget As Range
Dim blnWrongInput As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
  'if we are in column D and the len of input > 4 then
  If ((Target.Column = 4) And (Len(Target.Value) > 4)) Then
    'set copy mode to false
    Application.CutCopyMode = False
    'show warning
    MsgBox "sorry not more then 4 characters try again"
    'set the bool to true
    blnWrongInput = True
    'remember the wrong input cell
    Set rngTarget = Target
    'now set the focus back
    Target.Activate
  Else
    'else clean up
    Set rngTarget = Nothing
    blnWrongInput = False
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'if the bool is true AND our range is still filled then
  If (blnWrongInput = True) And (rngTarget Is Nothing) = False Then
    'check var length
    If (Len(rngTarget.Value) > 4) Then
      'if we're still trying to screw it up then set the focus back
      rngTarget.Activate
    End If
  End If
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
You could also use Conditional Formatting to color the offending cells. No VBA required.

Select the entire column (or range), the open the Format...Conditional Formatting dialog.  Choose Formula Is and then enter
=LEN(D1)>4     then choose Format...Patterns (from the same dialog) and select your color. You may have to repeat the step, because Excel likes to "improve" your formula by adding quotes--which you should then remove.

This formula will be treated in a relative sense, so each cell in column D will be checked for length--if more than 4 characters, you get the color. Note that if you choose a range, rather than the entire column, then the formula should reference the first cell in the range.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.