Excel 2007 Security and VBA

 Hi all,

 I'm posting here because this is potentially a VBA problem with Excel 2007. It seems that most people are unable to use .xlsm files that have password protected VBA projects in them. Or, it may be related to worksheet/workbook protection. I'm not sure.

Ron  de Bruin has a nice post on a workaround on his site at http://www.rondebruin.nl/password2007.htm. Unfortunately, I can't let my students have easy access to my VBA because then they could very easily defeat my security measures, and it really isn't practical for me to ask them to do a registry tweak.

Now, the files that I recently sent out have password protected VBA, and the worksheets are all protected with a password, and the workbook is protected with a password. There is no password required to open the file and browse through it. These are returned (graded) exams, so you can understand why I need to keep these files from being copied (the VBA prevents most methods of extracting the info).

These files work perfectly fine on two of my PCs and I never applied the registry tweak that Ron mentions. Why? Microsoft also discusses this problem on this page http://support.microsoft.com/kb/927150. They seem to say (I'm not a sys admin or security guru so I'm not sure that I understand fully) if you have an anti-virus program that supports the Microsoft Antivirus API then you won't have this problem. Is that true? I use Eset NOD32 on one machine and AVG Free 7.5 on the other. Both PCs handle these files without difficulty.

So, does anybody know exactly what triggers this problem? Is it the password protected VBA? The protected worksheets? The fact that the workbook is protected for structure?

If it is the password protected VBA project, then I'm afraid I'm screwed. If it is the worksheet/workbook protection then I can work around that. I think the password protected VBA is OK because I've been able to run files with that on our campus PCs. However, a workbook password (to open) makes those files unusable on campus.

Sorry for the long post. Thanks for any insight.

Tim 

What are the ramifications of saving your workbook as an .xls file.  Do you still have the same issue with .xls files?  Will something break?

 Hi Mike. No, .xls files work fine and that is what I used for many years. However, we no longer have Excel 2003 on campus so I made the switch. 

I've thought about this some more, and it appears that VBA being password protected is not the problem.  I realized that the exam workbooks that I gave them had a password protected VBA project and that caused no problem at all. So, it is the worksheet/workbook protection that is causing the problem. I can live with that. That was new code anyway, so I can just remove the protection and be back where I was. I never had a problem, that I know of, so I think my VBA protection measures foiled most attempts. Fortunately, my students aren't hackers.

I'll just do some RibbonX modifications to eliminate some troublesome things like being able to copy a sheet to another workbook. In Excel 2003 I had a couple of lines of code that disabled right-clicking the sheet tabs, the Edit menu, and the Tools menu. In 2007 the sheet tabs are still disabled, but there is no Edit or Tools menu so disabling those doesn't do the trick. You can still move or copy a sheet using the Format button on the Home tab. 

I can get around that by disabling the Cells group using RibbonX. That is just an extra hassle. I downloaded your Ribbon-customizing workbook to have a look at the code. I'm going to try to use that code so that I can create an add-in to swap in the appropriate customizations. I'll probably keep it really simple and just disable the Ribbon completely for returned exams.

I'm not sure if it works the same way in 2007 as in previous versions, but isn't it possible for students to just open the workbook without enabling macros?  Then they would be free to cut-copy or whatever they wished.

I seem to recall a post elsewhere on this forum echoing this..

ah, here: http://excelusergroup.org/forums/p/169/291.aspx#291 and http://excelusergroup.org/forums/p/486/1403.aspx#1403

Is the .xlsm format only able to be opened with macros enabled?

 Hi Rick,

 That was one of my original concerns years ago when I started doing this to secure exams. What I do is to set the visible attribute for each worskheet (except one that has a note to enable macros) to xlSheetVeryHidden before the workbook is saved. Then if macros are enabled, and the expiration date hasn't passed, the worksheets are unhidden upon opening the workbook. That security measure is easily defeated by anbody who knows a little VBA, but most of my students (all finance majors) have never done any coding. So, it is weak security, but it seems to be enough to deter them. I also employ many other techniques to try to keep them from sharing the graded exams with students who will take the class in the future.

That weakness is the main reason that I recently decided to also protect each worksheet and the entire workbook with passwords. It was this move that apprently triggered the problems in opening the files. I'm not yet sure how many students haven't been able to open the files (Spring Break), but I do know that at least one had no problems. 

All that I can think of at this point is that maybe the anit-virus program that you use is the determinant of whether you can open the files or not. As I said previously, I've been able to open the files on two PCs: one using Nod32 and another using AVG Free 7.5. 

Have you considered/tried the new xlsb fileformat? Maybe it lacks the problems you describe?