?
Solved

Help Disabling the "Unprotect" option in the menu bar of Excel

Posted on 2007-10-04
7
Medium Priority
?
700 Views
Last Modified: 2011-10-03
I have a spreadsheet that I do NOT want anyone to change. Rather than protecting it with a password I would like to disable the "Unprotect" option in the menu bar. Is this possible using VBA code?
0
Comment
Question by:pwken
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20013763
I don't understand - you will still have to protect the worksheet!
0
 

Author Comment

by:pwken
ID: 20013779
I plan on protecting the worksheet to allow input on specific "unlocked" cells. I do not want anyone to unprotect it and passwording can be an issue.
0
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 2000 total points
ID: 20013847
This can only be done using a macro. still if a user disables the Macro's then the user will be able to change the document.


to unprotect that option for a certain sheet you might think of code like:
Private Sub Worksheet_Activate()
    Dim ctrl As CommandBarControl
    Dim cb As CommandBar
    Application.CommandBars(1).Controls("Tools").Controls("Protection").Enabled = False
End Sub
Private Sub Worksheet_DeActivate()
    Dim ctrl As CommandBarControl
    Dim cb As CommandBar
    Application.CommandBars(1).Controls("Tools").Controls("Protection").Enabled = True
End Sub


Place this code in the worksheet code pane

Hopeing this will help you out.
regards,
Jeroen
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:peter57r
ID: 20013859
And you're going to protect the code that does this by........?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20013897
Bear in mind that they can also customise their toolbar and drag a new 'Unprotect sheet...' control onto it.
0
 

Author Comment

by:pwken
ID: 20013907
Perfect Thanks!
0
 

Author Comment

by:pwken
ID: 20014084
Thanks for sharing your concerns. My goal here is to "Strongly" discourage people from changing anyhting on the spreadsheet. The problem that I run into with passwords is that they can be changed without my knowledge and then I have a real problem. This ia an attempt at protection without a password.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

839 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