Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
"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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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...8d-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 , 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
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.generaldiscu ssion? Some experts on OneCare may help you resolve the problem. But please note that the queue microsoft.public.windows.live.onecare.generaldiscu ssion is not managed by Microsoft newsgroup team, so it is not a Managed Newsgroup (http://msdn2.microsoft.com/en-us/sub...aa974230.aspx). Sorry for the inconvenience. 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Change macro doesn't run in 2007
(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.generaldiscu ssion? Some experts on OneCare may help you resolve the problem. But please note that the queue microsoft.public.windows.live.onecare.generaldiscu ssion is not managed by Microsoft newsgroup team, so it is not a Managed Newsgroup (http://msdn2.microsoft.com/en-us/sub...aa974230.aspx). Sorry for the inconvenience. 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Worksheet, Change Query Parameters 2007 vs 2003 | Excel Discussion (Misc queries) | |||
change direction worksheet excel 2007 right to left | Excel Discussion (Misc queries) | |||
How do I change the color of the MS 2007 worksheet tabs? | Excel Worksheet Functions | |||
Change Macro Button Color 2007 | Excel Discussion (Misc queries) | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming |