Worksheet_change event problems

[Ken Schobloher]

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 & "" & rng.Row)
.Locked = False
.Value = 0
.Locked = True
End With
Case "Even"
' Split the amount evenly across the year
With Range("E" & rng.Row & "" & 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

 

[OssieMac]

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?

 

[Ken Schobloher]

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]

Hi Ken,

I am afraid that I am lost in the logic of what you are doing.
Worksheet_Change event is called if any cell on the worksheet is changed.

Your code does not identify what range has changed and this event can be
called if only one cell is changed. (Application.Intersect is usually used to
test if changed cell falls withing the required range for processing to
continue.)

For Each rng In Target
This indicates a number of cells are in the range Target. What changes a
number of cells simultaneously to trigger the event and then have Target as a
range to use the For Each....?

Also any change you make to a cell withing the event code will cause the
event to be called again. Seems to me that it should have
Application.EnableEvents = False as the first line of the code and then turn
events back on at the end of the sub.

If using Application.EnableEvents = False then you should have a little sub
like the following that you can run from the VBA editor if your code fails
during testing otherwise the events remain turned off.

Sub ReEnable_Events()
Application.EnableEvents = True
End Sub

Perhaps you have not posted all the code.

Also, when using identical code for multiple sheets the usual practice is to
place the code in a module and simply call the sub from the event routine
like the following. then when you have it working it should work with all
worksheets.

Private Sub Worksheet_Change(ByVal Target As Range)
'maybe If code here to identify if required cell has changed
call MySub("SheetName")
end sub

Then in the module:

Sub MySub(shtName as string)

'code here

end sub