Worksheet Change Event With Validation List

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_TotalLabour_Rev/SUM(PF_BareLabour,(PF_ContractLabour/PF_BurdenMultiplier)))"
.Locked = True
End With
Case Else
'Nothing
End Select
End If
Application.EnableEvents = True
End Sub

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

Kris.

Worksheet_change event doesn't work correctly if you change value from
drop down list.
It works if you type value manually.
It' s a bug in excel since I even don't rememeber and nobody wants to
fix it.





Kris_Wright_77 wrote:
> 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_TotalLabour_Rev/SUM(PF_BareLabour,(PF_ContractLabour/PF_BurdenMultiplier)))"
> .Locked = True
> End With
> Case Else
> 'Nothing
> End Select
> End If
> Application.EnableEvents = True
> End Sub
>

Tom

I ran the code cleaner, and it didn't help.

So resorted to making a copy of the problem sheet, and discovered that it
relates to the Conditional Formatting that I had applied to range with the
formula.
Once I deleted the Conditional Formatting from the original sheet, the
change event started running again with the validation list.

I dont understand why it should make a difference, unless the UDF I have is
incorrect.
Function IsFormula(Cell)
Application.Volatile
IsFormula = Cell.HasFormula
End Function

But for now I will just use code to change the format.

Thanks for all your help

Kris

"Tom Ogilvy" wrote:

> 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