Worksheet.Change macro doesn't run in 2007

[David Walker]

I converted an Excel 2000 spreadsheet to Excel 2007, to an xlsm file
(macros allowed). It has a very simple Worksheet Change macro stored in
the code "behind" one of the worksheets:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Balance entered?
If Target.Column = 5 Then Target.Offset(0, 1).Value = Date

' Amount paid entered?
If Target.Column = 11 Then Target.Offset(0, -1).Value = Date

End Sub

This macro shows up in the xlsm file when I right-click the worksheet tab
and select "View Code" in the context menu.

The file is password-protected, so I have to enter the password to open
it.

The macro doesn't run at all. I have tried putting a Stop command after
the Private Sub line, so I know the macro doesn't get called when I
change the value of a cell. It should get called for a change to any
cell on that worksheet.

What I have tried:

In the Trust Center, I have the path that this file comes from, listed in
the "Trusted Locations".

The Macro Settings are set to disable macros NOT in a trusted location,
WITH notification... this shouldn't apply, since the file is in a trusted
location, but I'm not getting notified anyway.

I have Message Bar set to tell me if content has been blocked.

I searched Google to try to find out what could cause this not to run the
same way in 2007 that it did in 2000.

Is there something I need to do, to turn on events in Excel 2007?

Thanks.

David Walker

 

[Bill Renaud]

In Excel 2000 (what I still use), you turn on events with:
Application.EnableEvents

Do you have another macro or routine that might have turned events off
for some reason? This could happen if a macro crashed while executing
after the events had been turned off for some reason.

Otherwise, I can't tell you about how Excel 2007 behaves (especially
under Windows Vista, if that is what you are running).

 

[Jialiang Ge [MSFT]]

Hello David,

From your post, my understanding on this issue is: you want to know why the
Worksheet_Change event is not fired for a protected Excel workbook which is
updated from Excel 2000 to Excel 2007. If I'm off base, please feel free to
let me know.

I tested a simple Excel 2000 xls file which only contains the VBA codes you
pasted here. In the mean time, I set a password to protect the file from
opening. Then I open the workbook in Excel 2007 (with the password) and
save it as xlsm into a trusted directory. When I reopen the xlsm, the macro
works well in my side. In order to troubleshoot your issue, would you let
me know the information below so that I can provide further assistance on
this problem. I am looking forward to your reply.

#1. As Bill suggested, would you check whether there is any other macro or
self-designed Office add-in that turns off the Applicatoin.EnableEvents
property?
#2. Would you create a Excel 2007 xlsm file directly with Office 2007, and
copy all your data and macros into it. If the Worksheet_Change event is
fired in this workbook, I think there might be some error when the xls file
is updated. Otherwise, the problem may lie in the macro itself. Please
double check the first suggestion or send the xlsm to my mail box and I
will do the tests for you.
#3. Have you ever tried to open the xlsm outside the trusted directory?
Does it give a notification of macro when the workbook is opened?

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hi David,

Would you mind letting me know the result of the suggestions? If you need
further assistance, feel free to let me know. I will be more than happy to
be of assistance.

Have a great day!

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Walker]

(e-mail address removed) (Jialiang Ge [MSFT]) wrote in

Hi David,

Would you mind letting me know the result of the suggestions? If you
need
further assistance, feel free to let me know. I will be more than
happy to be of assistance.

Have a great day!

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

I discovered that macros are disabled in the workbook. I can't do
ANYTHING to get macros to run in that workbook. I have trusted myself
with a self-generated certificate, signed the project, loaded the file
from a trusted location, and even tried enabling all macros temporarily.
Nothing works. Macros in that workbook are disabled (but ONLY in Excel
2007 -- they work fine in Excel 2000 -- I still have an Excel 2000 copy
of this file.)

I don't know why macros would get disabled in this file, especially
without a notification showing up somewhere. (THAT sounds like a bug.)

I am running Beta 2.0 of Windows Live OneCare, but I can create a new
workbook, and macros are enabled in it, so I know that OneCare is not
stopping all macros from running.

Bill: you said "Do you have another macro or routine that might have
turned events off for some reason? This could happen if a macro crashed
while executing after the events had been turned off for some reason."

Nothing has crashed. I am not running Vista either, just XP Pro. I
don't have any macros that turn events off, and I don't have any macros
that were written by anyone else.

As for the other suggestions, Jialiang, I will try them and let you know.
I have a headache right now and I have to quit, but I just wanted to
check back and let anyone who had answered, know that I hadn't forgotten.
(My original post disappeared, but the answers are here. Hmmm...)

Maybe creating a password protected Excel 2000 file with macros, then
saving THAT as an Excel 2007 xlsm file, might possibly show a bug. I'll
have to check that also. Maybe this particular workbook is just too old
and creaky.

Thanks. I'll try to check this out tomorrow. Probably, copying all of
the sheets and code-behind into a new workbook will fix the problem. I
appreciate the help and I'll let you know.



David

 

[David Walker]

In Excel 2000 (what I still use), you turn on events with:
Application.EnableEvents

The macro works fine in my Excel 2000, which I still have, and I still have
an Excel 2000 copy of the file. But see my other post.

Thanks.

David

Do you have another macro or routine that might have turned events off
for some reason? This could happen if a macro crashed while executing
after the events had been turned off for some reason.

Otherwise, I can't tell you about how Excel 2007 behaves (especially
under Windows Vista, if that is what you are running).

Thanks.

 

[Jialiang Ge [MSFT]]

Hello David,

Would you mind letting me know the result of your trial? If you feel it is
inconvenient, please feel free to send the Excel 2000 file to my mailbox
and I will do the test for you. I will be more than happy to be of
assistance.

Have a great day!
Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Walker]

(e-mail address removed) (Jialiang Ge [MSFT]) wrote in

Hello David,

Would you mind letting me know the result of your trial? If you feel
it is inconvenient, please feel free to send the Excel 2000 file to my
mailbox and I will do the test for you. I will be more than happy to
be of assistance.

Have a great day!
Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Apparently macros don't run once the workbook has been password-
protected.

I'll bet that you can reproduce this problem at your end:

1) Create a new, empty Excel 2007 file, add a macro, and save it as an
xlsm file (macroi-enabled workbook). The macro can be simple like this:

Sub Test()
x = 9
End

2) Open the file. Go to Visual Basic and notice that macros are enabled.
(There are several ways to see if macros are enabled. One way is to
click Visual Basic on the Developer tab, open an Immediate window
(View/Immediate Window), and type something like "?
Application.EnableEvents" without the quotation marks, and press Enter.
You should get the response True in the Immediate window. (This means
that events are enabled, and also it tells you that Visual Basic is
working, and thirdly that macros are enabled in the workbook.)

3) Click on the Office button, Prepare, Encrypt Document.

4) Save the file.

5) Open the file. Go to Visual Basic again, type "?
Application.EnableEvents" in the Immediate window, and press Enter.

You will get a dialog box that says "Macros in this project are
disabled".

Now, WHY are macros disabled? Plus, why is there no warning that macros
are being disabled when you open the file?

THAT is two bugs right there -- the macros are disabled, and there's no
warning that they are disabled. (If macro security is set to disable
with notification, then you should be told when you open the workbook
that Excel is disabling the macros.)

It doesn't matter what your macro security settings are; even "Enable all
macros" will still result in the macros being disabled in a password-
protected workbook.

P.S. Google shows a workaround:

Create a DWORD value of 1 here:

HKCU\Software\Microsoft\Office\12.0\Excel\Security
\ExcelBypassEncryptedMacroScan

Even though this workaround might work, this is STILL two bugs. Excel
should not disable macros, and if it does, it should tell you. I don't
know what the workaround does -- it sounds like it doesn't "scan" macros
in encrypted workbooks, whatever that means -- but changing the registry
should not be required for Excel 2007 to work right.

Your thoughts on this?

David Walker

 

[Jialiang Ge [MSFT]]

Hello, David

Thanks for your detailed steps. I reproduced the issue when I disabled my
anti-virus software and did the steps you described in the last reply.
According to the KB article http://support.microsoft.com/kb/927150, this
behavior occurs if the computer is not running an active antivirus program
that supports the scanning of encrypted content as supported by the
Microsoft Antivirus API. If your virus-scanning program does not support
the Microsoft Antivirus API, the virus scanning progrm cannot scan encryped
macros. As a result, encrypted macros will be disabled. To prevent the
antivirus program from scanning encrypted macros, please refer to the
secion "Prevent encrypted macros from being scanned fro viruses" in page:
http://technet2.microsoft.com/Office/en-us/library/83308fb0-db8d-484b-a5ae-0
757c162076b1033.mspx?mfr=true

Another resolution, as the KB http://support.microsoft.com/kb/927150 said,
is to update the antivirus program to a program that supports the scanning
of encrypted content as supported by the Microsoft Antivirus API.

If you have any other concern, please feel free to let me know. We
appreciate it a lot for your feedbacks on our products. Thank you.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Walker]

Please see below.

(e-mail address removed) (Jialiang Ge [MSFT]) wrote in

Hello, David

Thanks for your detailed steps. I reproduced the issue when I disabled
my anti-virus software and did the steps you described in the last
reply. According to the KB article
http://support.microsoft.com/kb/927150, this behavior occurs if the
computer is not running an active antivirus program that supports the
scanning of encrypted content as supported by the Microsoft Antivirus
API. If your virus-scanning program does not support the Microsoft
Antivirus API, the virus scanning progrm cannot scan encryped macros.
As a result, encrypted macros will be disabled.

My antivirus program is Windows Live OneCare. I would hope that Windows
Live OneCare supports the Microsoft Antivirus API.

To prevent the

antivirus program from scanning encrypted macros, please refer to the
secion "Prevent encrypted macros from being scanned fro viruses" in
page:
http://technet2.microsoft.com/Office/en-us/library/83308fb0-db8d-484b-a
5ae-0 757c162076b1033.mspx?mfr=true

I saw that, but according to the first KB article, I don't really want to
stop my antivirus program from scanning encrypted macros: Windows Live
OneCare should be able to scan them, assuming that it supports the
Microsoft AntiVirus API.

Plus, a normal user shouldn't have to hack the registry to have Excel
2007 work correctly with Windows Live OneCare. Right?

Another resolution, as the KB http://support.microsoft.com/kb/927150
said, is to update the antivirus program to a program that supports
the scanning of encrypted content as supported by the Microsoft
Antivirus API.

I am using Windows Live OneCare. I assume that program supports the
Microsoft AntiVirus API.

This appears to be a bug in either Excel 2007 or in Windows Live OneCare.

What do you think?

David

 

[Jialiang Ge [MSFT]]

Hello David,

I tried an encrypted xlsm file in a computer with Windows Live OneCare, and
the issue is reproduced. Excel 2007 pops out a Security Alert which says
that this file contains encrypted macros that have been disabled because
there is no antivirus software installed that can scan them. But the xlsm
performed as expected in another computer with e-Trust. Therefore, I think
that it might be an issue of OneCare. Would you mind posting an question to
check if OneCare supports Microsoft Antivirus API in the queue
microsoft.public.windows.live.onecare.generaldiscussion? Some experts on
OneCare may help you resolve the problem. But please note that the queue
microsoft.public.windows.live.onecare.generaldiscussion is not managed by
Microsoft newsgroup team, so it is not a Managed Newsgroup
(http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx). Sorry for
the inconvenience.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Walker]

(e-mail address removed) (Jialiang Ge [MSFT]) wrote in

Hello David,

I tried an encrypted xlsm file in a computer with Windows Live
OneCare, and
the issue is reproduced. Excel 2007 pops out a Security Alert which
says
that this file contains encrypted macros that have been disabled
because there is no antivirus software installed that can scan them.
But the xlsm performed as expected in another computer with e-Trust.
Therefore, I think that it might be an issue of OneCare. Would you
mind posting an question to check if OneCare supports Microsoft
Antivirus API in the queue
microsoft.public.windows.live.onecare.generaldiscussion? Some experts
on OneCare may help you resolve the problem. But please note that the
queue microsoft.public.windows.live.onecare.generaldiscussion is not
managed by Microsoft newsgroup team, so it is not a Managed Newsgroup
(http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx). Sorry
for the inconvenience.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Hello. I was not getting the message that there is no antivirus
installed that could scan the macro. (This is strange. And this sounds
like a bug in Excel.)

I was planning to check with OneCare, but as of today (but not yesterday)
the macros are running! I don't know if OneCare pushed out an update
that I did not know about.

So, it seems to work now.

Thanks for your help. If the problem comes back again, I'll ask
OneCare.

David Walker