Peter T-
Your comments did get me to thinking and trying some different things. I
have 3 worksheets in the workbook that use their respective Calculation
Events. Only the one ever returned a noticable error, so I didn't think much
about the other 2. After your suggestion to rework them using the Change
events instead, I did this with all 3 and, what do you know, no more errors
and I can save and close without problems. That seems to have been the
problem.

Still, I can't explain why the problems only occured with automatic
activation of macros and not manual. While I may not have an explanation, I
do now have a fix! So thank you very much for your help!

Elkar


"Peter T" wrote:


"Elkar" wrote in message

Comments in-line

On opening, I receive the error: "Run-time error '424': Object Required".
Hitting Debug points me to these lines (depending which condition is met):

SubmitReport.Enabled = True
SubmitReport.Enabled = False

SubmitReport is a button on the worksheet 'Reconcilliation'.

Sounds like there is no ActiveX control named "SubmitReport" on the sheet
whose code module includes the calculate event. Is the sheet named
'Reconcilliation' that which includes the calculate event. Is 'SubmitReport'
an aX control.

I only want
the button to be activated under certain conditions. I placed the code in
the Calculation event instead of the Change event because of a ComboBox
that
is linked to the 'Data' worksheet instead of the 'Reconciliation'
worksheet.
Thus, a Change event would not be triggered at the appropriate time.

Cell M8 contains the formula:
=IFERROR(IF(AND(E5=I5,(E6+E7)=I6,E8=I8),"BALANCED" ,"NOT BALANCED"),"NOT
BALANCED")

And Data!T2 is populated by a ComboBox.

From what you describe above the calculate event could (probably) be
replaced with two Change events, one in the same module to check if any of
the cells E5:E8,I5,I8 have changed (assuming these are not formula cells)
and another in the Data2 code module to check T2

However this is not the main issue, just generally preferable instead of
using the calculate event.

There is no code that runs when the workbook is closed or saved. Excel
just
crashes and I'm asked if I want to attempt to recover my file (which
doesn't
work if I say "yes").

If there really is no event code that fires when saved and/or (btw which?)
closed it is indeed a mystery as to why all is OK with the macro security
settings, but not otherwise. Doesn't make sense at all.

I should also note that even when I remove the entire Calculation Event,
the
opening error disappears, but the crash on save/close still occurs.

Depending on settings the workbook (typically) recalculates when opened. Put
your cursor in the calculate event and step through it with regard to what I
mentioned above.

Regards,
Peter T



"Peter T" wrote:

Very difficult to say without knowing more about what that "ton of code".
Eg,

Describe "posts an error", what's the error and on what line.

What is SubmitReport.

Why do you have code in the calculate event instead of (say) a change
event
that checks if the changed cells include M8 (in the sheet code) and
data!T2
(or if those are formula cells any relevant precedents)

What event code runs when you save and/or close the workbook (does that
calculate event get triggered).

Regards,
Peter T


"Elkar" wrote in message
...
This one really has me stumped, so I'm hoping someone has an idea what
might
be causing this. I'm using Excel 2007 SP2 in a Windows XP SP3
environment.
The workbook was created in 2007 and never existed in an older format.

I have a workbook with a ton of code in it. Everything works exactly
as
it's supposed to. The problem only occurs when I change the macro
security
settings.

If I leave the security level at "Disable all macros with notification"
and
then manually enable content each time the workbook is opened, then no
problem. But, if I change the settings to "Enable all macros" or set
up
the
save folder as a Trusted Location, then 2 problems suddenly start to
happen.
The results are the same on 4 different computers.

First, this block of code immediately posts an error:

Private Sub Worksheet_Calculate()

If Range("M8").Value = "BALANCED" And
Worksheets("Data").Range("T2").Value < "" Then
SubmitReport.Enabled = True
Else
SubmitReport.Enabled = False
End If

End Sub

Second, whenever I try to save or close Excel, the application crashes
and
all changes are lost.

So, basically, if I manually enable macros, its fine. If I
automatically
enable macros, it doesn't work.

I suppose we can live with manually enabling macros each time, but I'm
still
really curious what is causing this problem.

Any ideas??
Thanks!
Elkar


.



.