ExcelBanter - Worksheet Change Event With Validation List

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Worksheet Change Event With Validation List (https://www.excelbanter.com/excel-programming/351317-worksheet-change-event-validation-list.html)

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

Worksheet Change Event With Validation List

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

Worksheet Change Event With Validation List

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

Worksheet Change Event With Validation List

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

Worksheet Change Event With Validation List

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

Worksheet Change Event With Validation List

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