Solved

Excel 2010 VBA Code Protection

Posted on 2011-02-27
18
1,102 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now