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'. 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.

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").

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.


"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


.