Solved: Problems Working with Protection

[tkaplan]

I have a sheet in excel with the following code behind it:
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

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!!!:banghead:

[shades]

Howdy. Just a hint: It does not help to title the thread "Urgent". It tells us nothing about what you want, it does nothing for those searching for a topic, and finally, some people might ignore the thread because of the demand "urgent". What is urgent for you may not be for those offering free help.

So, I encourage you to put a more descriptive title on the thread - for your sake, and everyone elses.

[matthewspatrick]

... 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!!!:banghead:

Simple solution: use Me.Unprotect "mypassword" at the beginning of the sub and Me.Protect "mypassword at the end.

It's doing it because

You can't edit locked cells on a protected worksheet and
The error this would have thrown is being handled by the error handler.

[tkaplan]

thank you shades for the advice:)
matthews- i tried what you suggested and it is still doing it. i looked at the format of the cells and made them unhidden and that fixed it.
so now i'm just wondering, why would it matter if the cells are hidden or not?? the user will be typing a lot of text here so i did not want it to display in the formula bar, just on the screen. but if hiding the cell deletes the data, that poses a much bigger problem for me.
\
any advice???

[shades]

Can you change this to UserInterfaceOnly? It accomplishes the same thing as protection but this allows the code to continue to work.

[tkaplan]

Can you change this to UserInterfaceOnly? It accomplishes the same thing as protection but this allows the code to continue to work.

How do you do that?

[shades]

Check out this thread on UserInterfaceOnly (http://www.vbaexpress.com/forum/showthread.php?t=5169&highlight=UserInterfaceOnly)

[tkaplan]

still happens.
it must be something to do with the fact that the cells are hidden, i just cant figure out why that should affect it.

[shades]

There is a difference between "locked" protected and "hidden" protected. Which are you using, and why?

[tkaplan]

it is hidden but not locked.
the reason i am doing it is because this is a comments box where the user can put a ton of text in it and as they put in more text, the formula bar grows and begins to hide things in the sheet that they may want to see while they are typing their comments. by keeping it hidden, the formula bar stays blank while they type (or thats what i was hoping for at least...)

[geekgirlau]

Hi tkaplan, I've modified your thread title to make it more descriptive.

[geekgirlau]

I experienced the same problem. What about hiding the formula bar just when the user clicks in your target range? They can still double-click on the cell and edit directly in the cell, but the entire formula bar is hidden. Try this when the sheet is not protected (and you can remove the Hidden option as well).


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRngToInspect As Range


If Target.Cells.Count = 1 Then
Set myRngToInspect = Me.Range("B9:B11")
Application.DisplayFormulaBar = Intersect(Target, myRngToInspect) Is Nothing

Else
Application.DisplayFormulaBar = True
End If
End Sub

[tkaplan]

ok, that's a really good idea that will work:) i'm going to hide the formula bar when the sheet is activated and unhide when its deactivated.

thank you for that.
tkaplan