Solved

Excel 2010 VBA Code Protection

Posted on 2011-02-27
18
1,108 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Six Sigma Control Plans

617 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