vba for excel, find number of characters in a cell

[Koisti]

Sometimes the orders I deal with have a comment attached to them which can run very long once and a while. Is there a way to have vb check if the length of the comment exceeds a certain amount of characters and if so, then cut the rest off of the end and move them to the cell below?

 

[rmikesmith]

koisti,

Try something like the following:

Const MaxLength = 20

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not (Application.Intersect(Target, Range("C:C")) Is Nothing) Then
    If Len(Target.Text) > MaxLength Then
      Target.Offset(1, 0).Value = Mid$(Target.Text, MaxLength + 1)
      Target.Value = Left$(Target.Text, MaxLength)
    End If
  End If
End Sub

This code is placed into the code module for the appropriate worksheet. In the VBA editor, double-click the worksheet in Project Explorer. Select Worksheet from the left hand dropdown, then Change from the right-hand dropdown. Make the Change event procedure look like the above. Don't forget to add the maximum length constant. My example inpects which cell was changed to see if it is in column C. If so, it checks the length of the cell contents and if they exceed MaxLength places that portion into the cell below. The test for the Target cell range can be changed to suit your purpose (e.g. perhaps it should be compared against a single cell address).

HTH
M. Smith