Hi OssieMac,

The drop down data validation selects a method of spreading an anual budget
(i.e. evenly accross the 12 months, quarterly, specific). The value is
selected by the user from the drop down list, this is working correctly.

The program code works fully on the first (budget sheet). The same code
applied to the salary sheet fails. All of the cells that I want to modify
with the event are locked and the sheet is password protected with
UserInterfaceOnly option.

With error trapping off, stepping through the code, the .Locked = False does
not uplock the cell, but returns no error. The next line .value = 0 sends me
to a UDF that calculates payroll taxes, skipping any remaining code in the
event. Calling the UDF would be expected as the cell with the drop down is a
dependent argument.

Thanks,
Ken


"OssieMac" wrote:

Hi Ken,

Firstly can you comment out the On Error GoTo ErrHandler and then tell us on
what line the code is failing.

Next you said that you have drop down data validation. Does the data you are
entering in the cell meet the data validation criteria?

--
Regards,

OssieMac


"Ken Schobloher" wrote:

I am creating budget templates that use the worksheet_change event to modify
formulas in other cells of the sheet. One sheet is for budget and other is
for salaries. On the budget sheet everything works like it should, on the
salary sheet the cell unlock is ignored and the update traps to my error
routine. The column contains a drop-down validation that I am testing. Is
there something I am missing?

Here is a portion of the code that works on one sheet but not the other:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Not (Target.Interior.ColorIndex = 39 Or Cells(Target.Row, 4).Value =
"Specific") Then Exit Sub

' On error restore operation of this code, then disable events till we are
done here

On Error GoTo ErrHandler
Application.EnableEvents = False

' Process each of the changed value in succession

For Each rng In Target
' Setup the formulas based on the selected spreading means
If rng.Column = 4 Then
Select Case rng.Value
Case "n/a"
' No Formulas for this row
With Range("E" & rng.Row & ":P" & rng.Row)
.Locked = False
.Value = 0
.Locked = True
End With
Case "Even"
' Split the amount evenly across the year
With Range("E" & rng.Row & ":P" & rng.Row)
.Locked = False
.Formula = "=$C" & rng.Row & "/12"
.Locked = True
End With
Case "Front Qtr"
' Split the amount in the 1st month of each quarter
For i = 1 To 12
With rng.Offset(0, i)
.Locked = False
If i Mod 3 = 1 Then
.Formula = "=C" & rng.Row & "/4"
Else
.Value = 0
End If
.Locked = True
End With
Next i