Worksheet Change Macro Help

[Vick]

I need a worksheet change macro that can do the following. For cells B16,17,
18, 19, 20, 24 - 28, and 31 -34, i would like to have those cells checked for
cell lengths greater than 1024 characters. If they are over 1024 characters
display a msg box stating that. I would also like it to automatically resize
the row on which the cell is changed. I can do this outside of a worksheet
change, but I would really like this to be automatically done for the user
when they change one of the cells listed above. Any help is greatly
appreciated.

Thanks

 

[JLatham]

I think this might do it for you, I haven't actually tested it, but it looks
right.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Or Target.Cells.Count > 1 Or _
Target.Row < 16 Or Target.Row > 34 Then
'not in column B, or more than one cell changed
'or above/below rows we're interested in
Exit Sub ' not in column B
End If
Select Case Target.Row
Case Is = 16, 18, 19, 20, 24, 25, 26, 27, 28, 31, 32, 33, 34
If Len(Target.Text) > 1024 Then
MsgBox "Text length is greater than 1024 characters.", _
vbOKOnly, "Cell: " & Target.Address
Exit Sub
End If
Target.Rows.AutoFit
Case Else
'all other rows ignored
End If
End Sub