Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
Data Validation Listbox and the Worksheet Change Event | Excel Programming | |||
Sheet change event and list validation question | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |