Protected Excel Worksheet

[robin.coe]

I have an Excel worksheet that contains questions, the cells with the
questions have been protected to prevent users from changing the
questions. The users are to type the answer in the cell beside the
question (I.E. question in protected cell A1 answer goes in
unprotected cell A2). However, if a user copies the answer from
another document and pastes the answer into cell A2 the information is
then protected and does allow them to change the answer that was
pasted into cell A2. Can someone explain why this is happening?

 

[Dave Peterson]

When the user copies|pastes, then the user is copying the cell and all its
attributes--including the locked-ness of that copied cell.

You could try to tell the user to paste to (and probably copy from) the address
bar. Then the cell's attributes aren't copied.

You could also have an macro that fires when the user makes a change. This
event macro would change the locked property back to what you want.

If you want to try, then rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the newly opened code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToUnlock As Range
Dim myIntersect As Range
Dim PWD As String

PWD = "hi"

'whatever that range is...
Set RngToUnlock = Me.Range("A2:a9,c3:d92,e:e,j:M")

Set myIntersect = Intersect(Target, RngToUnlock)

If myIntersect Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:=PWD
myIntersect.Locked = False
Me.Protect Password:=PWD

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

======
Be aware that most macros will kill the undo/redo stack.