Thanks Per, my idea (see P.S.). However the error message does appear (Error
1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a
small test sheet to check this protection behavior with locking and unlocking
cells and it works perfectly. I suppose in the real program there is some
unwanted interaction between the Excel and VBA protection mechanisms. I'll go
over the Workbook code and modules again just in case...

P.S. In the full code there is Err = "<cellname" above every section and
Err = "" just above the ErrHand label, so if the code executes normally, the
ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell
name where it jumped out.

"Per Jessen" wrote:

Hi

If Sheet10 is the active sheet no error should appear, but as I read your
code, the macro will always execute the ErrHand code. If no errors occour
you need to skip the ErrHand and goto ActiveSheet.protect etc...

Snip...

End If
Goto ProtectSheet

ErrHand:
If Err < "" Then
Result = MsgBox("Error occurred excuting " & Err & " change event",
vbOKOnly, "Error")
End If

ProtectSheet:
ActiveSheet.Protect Password:="somepwd"
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Best regards,
Per

"nb0512" skrev i meddelelsen
...
In my application a user makes a choice using a dropdown box in cell k44.
Depending on the choice, I then run some code to put some data in some
other
cells, k59 and k61-64, which I subsequently want to protect
programmatically
from being changed manually.
I get an error saying the program cannot set the .Locked property of the
cells.

I have these cells unlocked in the sheet. In the code, I unprotect the
sheet
before attempting to change the cells' properties. Below is the code I
use.
Any ideas ?

Private Sub Worksheet_change(ByVal Target As Range)
...
...some Dim statements
...
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheet10.Unprotect Password:="somepwd"

On Error GoTo ErrHand

...
...some other stuff
...

Err = "k44"

If Not Intersect(Range("k44"), Target) Is Nothing Then

Select Case Range("k44").Value

Case "...."

Case "...."


Case "Customer"
Range("L51").Value = "0"
Range("K51").Value = "0"

Range("k59").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,2)"
Range("k61").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,4)"
Range("k62").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,5)"
Range("k63").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,6)"
Range("k64").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,7)"
Range("k59").Locked = True---------jumps to ErrHand
here------- Range("k61:k64").Locked = True

...
...some other stuff
...

End If

ErrHand:
If Err < "" Then
Result = MsgBox("Error occurred excuting " & Err & " change event",
vbOKOnly, "Error")
End If
ActiveSheet.Protect Password:="somepwd"
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub