I have a sheet in excel with the following code behind it:
[VBA]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxLen As Long
Dim myRngToInspect As Range
Dim myCell As Range
Dim TruncatedText As String
Dim cCtr As Long
maxLen = 1100
If Target.Cells.Count > 1 Then Exit Sub
Set myRngToInspect = Me.Range("B9:B11")
If Intersect(Target, myRngToInspect) Is Nothing Then Exit Sub
On Error GoTo errHandler:
Application.EnableEvents = False
cCtr = 0
For Each myCell In myRngToInspect.Cells
cCtr = cCtr + 1
If Len(myCell.Value) <= maxLen Then
' Exit For
End If
'save the chopped portion
TruncatedText = Mid(myCell.Value, maxLen + 1)
'put the truncated text back
myCell.Value = Left(myCell.Value, maxLen)
If cCtr = myRngToInspect.Cells.Count Then
'on the last cell
If TruncatedText > "" Then
MsgBox "You have entered the maximum comments allowed." & _
vbNewLine & "The following text was not entered: " _
& vbLf & TruncatedText & _
vbLf & "Please include any additional comments in a seperate file."
End If
Else
'put the truncated text at the beginning of the next cell
myCell.Offset(1, 0).Value _
= TruncatedText & myCell.Offset(1, 0).Value
End If
Next myCell
errHandler:
Application.EnableEvents = True
End Sub [/VBA]
When the sheet is unprotected, this works exactly like i want it to - if text in cell is longer than max allowed, it splits the text into the next cell.
Problem: When sheet is protected, if i doubleclick on the cell to edit what is already there and add more text, it deletes everything that was there.
Why is this happening and how can i change this????
I have 30 people currently using this sheet and I guarentee you they are all going to call me within an hour to fix it for them. PLEASE HELP!!!
Reply With Quote
)
Originally Posted by tkaplan
