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

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).
--
Regards,
Bill Renaud

"Bill Renaud" wrote in
:

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.

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 , 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/subscripti...ult.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/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

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

(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 , 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