Over protection in 2002 XP environment
 

Since you're changing multiple cells, maybe you should unprotect the worksheet
before doing this:

For intCounter = -6 To -3
objActiveCell.Offset(0, intCounter).Value = ""
Next intCounter

And you may want to change ActiveCell to Target (or just use Target directly).
Target is the cell you just changed.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim intCounter As Long

On Error GoTo errHandler:

Select Case Target.Value
Case "A", "B"
Application.EnableEvents = False
Me.Unprotect Password:="drowssap"

For intCounter = -6 To -3
Target.Offset(0, intCounter).Value = ""
Next intCounter

Target.Offset(0, -2).Value = 0

Me.Protect Password:="drowssap"
Application.EnableEvents = True

End Select

errHandler:
Application.EnableEvents = True

End Sub

The .enableevents stuff stops the code from being called when you change one of
the cells.


Michael wrote:

Hi
How can you protect and unprotect sheets in code in Excel
2002 where cell values are selected from a validated list.

In Excel 2000 SP3 this code works
Private Sub Worksheet_Change(ByVal Target As Range)
...
...
Select Case Target.Value
Case "A", "B"
Set objActiveCell = ActiveCell
For intCounter = -6 To -3
objActiveCell.Offset(0, intCounter).Value = ""
Next intCounter
ActiveSheet.Unprotect Password:="drowssap"
objActiveCell.Offset(0, -2).Value = 0
ActiveSheet.Protect Password:="drowssap"
Case ....

On the test install Excel 2002 break points suggest sheet
protection is detected and the change event is not executed

Many Thanks
Michael

--

Dave Peterson