Try running Rob Bovey's code cleaner utility on your workbook. It is a free
download at

http://www.appspro.com

If that doesn't fix it, then it might be time to think about replacing the
sheet as a minimum or moving everything to a new workbook.

--
Regards,
Tom Ogilvy


"Kris_Wright_77" wrote in message
...
Tom
Thanks for the quick response.

The Formula works fine if entered manually, as that was where I created it
initially. Its just that under certain conditions the formula isnt
appropriate.
Although it was when I tested that the macro would correctly insert the
formula that the event last fired using a validation list

I initially did think that it was because the code ended prematurely with
the events off, but I have run a macro to turn them back on, and it still
doesnt fire on a change using a validation list, but will on anything
entered
manually.

I have also done some further testing, and it seems that the problem is
confined to just the one sheet in the book.
I have added some change events to another sheet in the workbook, and they
fire properly when using a Validation list.

I presume I have inadvertently changed a property of the worksheet, but I
cant find it anywhere.

Thanks for any further help that you can give.

Kris

"Tom Ogilvy" wrote:

If you enter that formula manually, does it work?

As far as the event not firing, I suspect you have an error in your
code, so
when the event end prematurely, events are disabled.



--
Regards,
Tom Ogilvy

"Kris_Wright_77" wrote in
message
...
I am in the middle of writing a macro that runs on any change to a
specific
cell containing a validation list.

I tested that it worked with each bit of extra code that I added, and
it
worked fine for a while, but then it started to crash Excel and I cant
find
out what caused it.

And even more peculiar, if I start writing from scratch, the event is
no
longer triggered by changing the cell by the Validation list.

I have attached the offending code, in case there is something there
that
has altered a setting within Excel that I am not aware of.
If I create a Change Event in a new workbook it works with the
Validation
List, so cant be the fact that I have Excel 2002.

Thanks for any help that you can give

Kris

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = Range("PF_ContractType").Address Then
Select Case Target.Value
Case "Fixed Price"
'Multiplier Label Change
With Range("PF_MultiplierLabel")
.Value = "Labour Revenue Multiplier on Bare"
.Font.Bold = True
.Font.ColorIndex = 0
.Font.Italic = False
End With
'Remove Equivalent Multiplier Formula
With Range("PF_Multiplier")
.Value = Null
.Locked = False
End With

Case "Time Charge"
'Multiplier Label Change
With Range("PF_MultiplierLabel")
.Value = "Equivalent Labour Revenue Multiplier on Bare"
.Font.Bold = False
.Font.ColorIndex = 48
.Font.Italic = True
End With
'Add In Equivalent Multiplier Formula
With Range("PF_Multiplier")
.Formula =


"=IF(SUM(PF_BareLabour,(PF_ContractLabour/PF_BurdenMultiplier))=0,,PF_TotalL
abour_Rev/SUM(PF_BareLabour,(PF_ContractLabour/PF_BurdenMultiplier)))"
.Locked = True
End With
Case Else
'Nothing
End Select
End If
Application.EnableEvents = True
End Sub