Solved

Excel 2010 VBA Code Protection

Posted on 2011-02-27
18
1,106 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

759 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