Stumped - Problem with Macro Security settings?

[Elkar]

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

 

[Bob Phillips]

You should NEVER enable all macros, just too risky.

Have you tried putting it in a trusted location and running it from there?

 

[Peter T]

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]

Yes, setting up a trusted location was what I originally tried, which I did
mention. The 'enabling all macros' was just a test to see if the same errors
occured (which they do).

I normally always run security settings as "Disable with notification", and
then set up specific folders as trusted locations.

 

[Elkar]

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]

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

 

[Robert Crandal]

Just curious, but what are some reasons for "NEVER" enabling all
macros?? I only use macro enabled workbooks that I personally
create. I also expect my macros to run immediately or automatically
without any reminders or warnings that a macro is about to run
or whatever.

thank you

 

[Gord Dibben]

If you never load a workbook other than those you create yourself you can
get away with enabling all macros.

Those you get from the 'net or from other persons could pose a risk of
malicious code.


Gord Dibben MS Excel MVP

 

[Peter T]

All the advice is there for very good reasons. That said, I have never
downloaded or been sent or even seen a workbook that contains deliberately
malicious code. Over the years that amounts to a lot of workbooks from a
wide variety of sources. Bad code that does bad things is another matter.

Regards,
Peter T

 

[Elkar]

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

 

[Martin Brown]

Comments in-line


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.

More likely there isn't one at the point where the code executes when
macro security is set to automatic. In manual mode there is plenty of
time to draw any controls before this VBA code executes, but there could
easily be a race condition if the macros are fully enabled.

The graphic engine in XL2007 is asynchoronous and very slow.

Regards,
Martin Brown