Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I know it is possible to password protect an entire Excel workbook, but can I have the workbook open (no password) and yet password protect just the macro?

(I'm using Excel 2007.)

Update: to be clear, I'm looking to password protect access to viewing the macro, not to running it. So, everyone should be able to run it but only I can access the source code.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

To add to @music2myear's answer, you can also protect the workbook's VBA code, so the user can't read it to get the password. In the VBE just right-click on the project and choose VBAProject Properties and click the Protection tab.

share|improve this answer
    
+1 Better approach as it requires knowledge of other tools/techniques to crack a VBA password, whereas a little VBA knowledge could disable a code password check –  brettdj Oct 6 '11 at 8:30
    
Thanks, this works. Note that it is necessary to check the Lock project for viewing checkbox. Related question: it seems I need to create a new macro to unlock and read the existing macro. Isn't there an easier way? –  Wikis Oct 8 '11 at 20:09

You could make the macro ask for a password. So long as the user doesn't have the chops to go looking for the password in the VBA code itself this can be very, very easy.

Simply use an InputBox to ask for the password. If the input matches a given string you allow the macro to continue.

You could make it more complex but still relatively simple by translating the password text into numbers and performing mathematical operations on those numbers to create a simple "hash". Then you just take any Input and perform the same operations on it before running the compare.

This link shows how you can use this method to check for multiple possible passwords and do different things based on them: http://www.ozgrid.com/forum/showthread.php?t=68186&page=1

This link shows how to replace the input text with asteriks like a "real" password box: http://www.xcelfiles.com/API_09.html

share|improve this answer
    
very nice answer! +1 –  studiohack Oct 5 '11 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.