application.EnableEvents value changing to false

Hi all,

I have a spreadsheet that uses worksheet activation to run a few small macros, but the problem is that after I switch sheets, the macro for the activated sheets runs, but then the application.EnableEvents value mysteriously changes to false. so far I have tried the following fixes:
- Turned off macro security. no change
- put a watch on Application.EnableEvents to break the code when the value changes..no code break, and the value still changes to false.
- I tried adding the line Application.EnableEvents=True as the last line in every macro...It still goes false
the only way that I can get the spreadsheet to still function is to open the immediate window and type "application.enableevents=True" at every step.
Any ideas??

Hi oosterhg,

Upload a sample workbook and I'll dive through your code to figure out which event is triggering.

Medpack,

Thanks for the response.
Unfortunately I can not post the entire sheet due to it's proprietary nature. However, here is what info i can give:
There is a data sheet, and several charts. Each chart has an activation macro (code from one is shown below) that formats the chart to fit the entered data. The data sheet also has an activation macro that restores the formulas that the code below removes. Currently when I switch from the data sheet to the any chart the first time, the chart activation macro runs all the way through, but at some point has changed the EnableEvents to false. As a side note (not sure of the relevance) - this workbook was created in office 2007.

Private Sub Chart_Activate()

Check = Validate_Data(2)

If Check = "Yes" Then

Sheets("Thinking").Visible = True
Sheets("Thinking").Select
Application.ScreenUpdating = False


'Remove Chart Protection
Sheets("Trim & Viscosity Curve").Select
Sheets("Trim & Viscosity Curve").Unprotect

'Set max Range on the Primary X-Axis
ActiveChart.Axes(xlCategory).MaximumScale = Range("Q_MAX").Value * 1.05

'Set Max range on the Secondary X-Axis
ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = Range("Q_MAX").Value * 1.05 / 4.4028675

'Set max Range on the Y-Axis
n = Range("n").Value
ActiveChart.Axes(xlValue).MaximumScale = 500 * n

'Set Y-Axis Major Unit
ActiveChart.Axes(xlValue).MajorUnit = 50 * n

'Set Y-Axis Minor Unit
ActiveChart.Axes(xlValue).MinorUnit = 10 * n

'Set max Range on the secondary Y-Axis
m = Range("m").Value
'in order to auto scale the secondary y axis for the NPSH curve, un comment the next line and comment out the following line
'ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = m * 50
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 100

'Set secondary Y-Axis Major Unit
ActiveChart.Axes(xlValue, xlSecondary).MajorUnit = m * 5

'remove Core Data Protection
Sheets("Core Data").Unprotect

'Remove Chart warning
Sheets("Core Data").Range("MWarn").Value = ""

'Clear all zeros from original curve NPSH data
For Each c In Range("NPSH").Cells 'Worksheets("Core").
If c.Value = 0 Then c.ClearContents
Next

'Clear all zeros from viscosity curve NPSH data
For Each c In Range("VNPSH").Cells 'Worksheets("Core").
If c.Value = 0 Then c.ClearContents
Next

'Reactivate sheet and Chart Protection
Sheets("Core Data").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Core Data").EnableSelection = xlUnlockedCells
Sheets("Trim & Viscosity Curve").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Sheets("Trim & Viscosity Curve").Select
Sheets("Thinking").Visible = False
Application.ScreenUpdating = True

End If

End Sub

Just delete the content from your workbook and post it blank with just the coding in the modules/sheets/objects/etc. I don't see anything wrong with the above code that should disable events triggering other than maybe there's a property when you re-protect the sheets that you're missing.

Medpack,

Here is the blank file...Happy hunting, and thanks again!


Blank.xlsm

I tried diving through your workbook, couldn't find any issues with the coding even when I removed your "Application.EnableEvents = True".

I don't think 2007 should be causing these issues - What are your macro security settings set to?

Medpack,
I've tried changing the macro settings and the problem still occurs at all levels of security. This was never an issue with 2007, but only started showing up when we switched to 2010.

The only thing I can think of is maybe when you protect/unprotect sheets it disable events by default? Otherwise I couldn't find an issue. Try putting an enable events at the end of any protect/protect code and see if that resolves your issue.

Otherwise I think this is beyond my knowledge - sorry.

Or another solution would be to create a fresh workbook, transfer all your worksheets to the new workbook, save the workbook fresh as 2010 (this way all references are maintained easily to 2010). That's all I can think of to try, sorry again - maybe someone else here will have more knowledge.

/bump!

Thanks Medpack. Hopefully i don't have to start from scratch.....just renaming all 141 named ranges would take longer than I'm willing to commit

I've oficially given up on this issue. Thanks for all the input.