2

I have a workbook, which creates a second workbook (using ThisWorkbook.SaveCopyAs) to present the data nicely to users. It relies on macros for part of the processing.

However, I've been asked to make those macros inaccessible to users. They still need to run them, but to view/edit them should require a password. I can figure out how to do it using the GUI (VBA Editor -> right click VBAProject -> VBAProject Properties -> Protection, tick the box and enter a password), but I haven't found a way to do so using VBA. The Workbook.Protect function seems to lock down everything except VBA.

If I try to apply it to the source workbook, I get "Can't perform operation since the project is protected", so that doesn't work either.

I'm running 2010, but the workbook needs to be compatible with 2003, so no fancy new tricks. :(

Community
  • 1
  • 1
Margaret
  • 5,749
  • 20
  • 56
  • 72
  • If my answer doesn't help, searching on `VBA Project Properties via VBA` seemed to generate a handful of results. – jonsca Apr 27 '11 at 05:41
  • I can't replicate this. If I create a workbook with a password-protected VBA module, put a button on it which does a ThisWorkbook.SaveCopyAs, then hit it... it saves a workbook with a protected VBA project. Did you really mean ThisWorkbook, or did you mean ActiveWorkbook? And, if it's the latter, how are you putting the macros in there? – Chris Rae May 06 '11 at 17:23

3 Answers3

1

This is a method that describes setting the project password without using sendkeys http://www.standards.com/Office/SetVBAProjectPassword.html

0

I had a similar problem. I needed excel code to populate user created new sheets, but I wanted to password protect the project. It would work great as long as I did not use password proection, but Excel will not allow it to generate code in the new sheets with password protection.

The solution was interesting. Give your project an easy name or initials . Something people can find with ease.

You password protect your entire project and save the resulting file as an XLA (add on).

Then UNprotect the workbook, rename the file (just in case you make a mistake) and strip it of all the code, modules, classes, forms, etc. Save the new stripped Excel File.

Now open the new stripped file and add the xla in TOOLS/add ons. Then go to the VBA editor, References and find your project wiht a new name, and link (you may have to save and reopen once to find under referneces).

That's it. the new file, empty of all code, will operate using the old code but you will have no access to it without a password. At the same time, the old code can place the code on the new worksheets as before since the new workbook Project is not password protected

What I have found is that if you change locations, your users may need to find the link in the references, but I am sure you can create a macro to find and link automatically (that macro, of course, would be visible to the world).

I think this will solve any problem with protected code.

Good luck

Richard Low
  • 1
0

This post may be helpful to you. Note that in the comments it says you need to add vbeext1.olb.

jonsca
  • 10,218
  • 26
  • 54
  • 62
  • Oh *wow* that is an ugly hack. Anything that requires using SendKeys sounds likely to break at the first sign of trouble. :/. – Margaret Apr 27 '11 at 13:01
  • @Margaret You didn't say you wanted pretty :) lol As I mentioned above, there were some other pages that I had received in the search that I didn't really look at. I think this is one of those things that the people who are experts at it don't want to share how they did it on the net... – jonsca Apr 27 '11 at 13:08