Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with cell protection-Excel 2002 SP3 | Excel Discussion (Misc queries) | |||
what is ERP environment | New Users to Excel | |||
Protection of check boxes in excel 2002 | Excel Discussion (Misc queries) | |||
Excel 2002 Pivot Table Protection | Excel Discussion (Misc queries) | |||
Excel 2002 VBA App fails in Excel 2000 Environment | Excel Programming |