Solved

Excel 2010 VBA Code Protection

Posted on 2011-02-27
18
1,105 Views
Last Modified: 2012-05-11
I have been trying to lock my code (right-clicking on VBA Project Name from explorer bar on left of VBA Editor > Going to Project Properties > Going to Protection tab > Checking 'Lock Project for viewing' > Entering Password (twice) > Saving project > Saving Excel file > Closing and reopening).  When I do this it does not protect VBA project.  I can open it and view/edit it with no problem.  When I go back to Project Properties, my 'Lock Project for Viewing' is no longer checked and the password boxes are empty.  What am I doing wrong?
0
Comment
Question by:MsSherryG
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34992311
MsSherryG,

Did you press OK as in this string of instructions?

Entering Password (twice) > Press OK >Saving project > Saving Excel file > Closing and reopening).

Because you didn't mention it yourself.

Patrcik
0
 

Author Comment

by:MsSherryG
ID: 34992390
Yes Patrickab, I did.  Guess I omitted it in my description.  I do have the Excel file itself password protected also.  Could this have anything to do with it?  I use the password to open the file before I go through the above steps.  I have tried it both protecting on just "open" and protecting with "open to modify"
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34992421
MsSherryG: Please do the following

1) Create a blank excel file. Password Protect the file with password as one "1" to open it
2) Paste this in a module

Sub Sample
  Debug.Print "Hello"
End Sub

3) Password protect the VBA. Same password as above.

Close the file and upload it here.

I want to check something.

Sid
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:MsSherryG
ID: 34992602
Here you go SiddhartRout.  Thanks for your time.
Test.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34992654
There is something wrong with this file. I am unable to open it. I tried renaming it to zip as well but winzip says this file is corrupt. Can you open it?

Sid
0
 

Author Comment

by:MsSherryG
ID: 34992796
Okay, I am new to Office 2010, before when I did as you said I got a message that said "Cannot save Macro-free workbook with this feature "VB Project", please save file as Macro-Enabled file then try again, so I did (not sure message wording is exact but it said something like that).  So that was the file I sent you that you couldn't open. This time, I ignored that message and just saved as default Excel workbook.  Please see if you can open this one.  and again thanks. Test.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34992820
I cannot open this one as well.

I feel your pc is infected with virus. I could be wrong though.

Sid
0
 

Author Comment

by:MsSherryG
ID: 34992874
Strange.., no other issues.  Can you try this file? Test-again.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34992887
Yes this file works.

I set a password for the VBA. Now check it.

Sid
Copy-of-Test-again.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34992996
The file is protected so there's no way I can check it out without knowing the file password.

Patrick
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34993000
Patrick the password is 1 as I suggested in ID: 34992421

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34996825
The test.xlsm file you posted first is fine and its VBA project is locked. Did you close and reopen it after you locked the project? (you have to for the protection to take effect)
0
 

Author Comment

by:MsSherryG
ID: 34997526
Sorry everyone, now one of the files is showing the VBA as being protected (thanks rorya), but when I try to redo same actions it again isn't locking.  I apologize but I don't have time right now to play around with this mystery :)  I promise I will get back to it within the next couple of days and apply the credits... I do apologize!   (and rorya, do you think Sid just couldn't open it due to it being Office 2010?)
Ciao for now...
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34997847
No idea - I opened it in 2007.

One thing that might be the issue, is that if you haven't actually added any code or modules yet, you cannot lock the project in 2010 (or 2007 - works in earlier versions)
0
 

Author Comment

by:MsSherryG
ID: 34998401
Thinking that may be the answer rorya!  I'll test as soon as I can and award the points... Thanks a million.  I was basically just trying to prevent a user from changing a sheets property from 2 - xlsheetveryhidden.  I just have a sheet I don't want users to be able to unhide, so there really wasn't any code just a sheet property change that can only be done from VBE.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34998452
Yeah, unfortunately the behaviour changed (with 2007 I think). Unless there is actually code or a module, you can't lock the project for viewing. Annoying.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34998559
Strange. I was not able to open the file in 2007. And when I renamed it to zip, it told me that it is corrupted.

>> Rorya: Unless there is actually code or a module, you can't lock the project for viewing. Annoying.
I didn't know that. :) Learnt something new today :)

Sid
0
 

Author Closing Comment

by:MsSherryG
ID: 35132681
Thanks for your help
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question