need code VBA to protect an excel file

rapidito78840 · Jul 20, 2005

Hello:
I know they are programs to unprotec the sheet and to unprotect the book, but I know , they are a code in VBA to protect all the file, and this code do this:
when some one try to remove the password protection to the sheet and to the book, this code run and leave the file without work.
Please if somebody can share to me this code, its very apreciated, because I make an excel file and I need to share in other computer but I dont want to other people can unprotect the sheet and unprotect the book and then make changes and use this file.

thanks for your answers and Its very apreciated your help
regards

Smitty · Jul 20, 2005

How's this:

Option Explicit

Public ws As Worksheet
Const myPassword As String = "password" ' Set Password here

Sub ProtectAll()
 For Each ws In ActiveWorkbook.Worksheets
 ws.Protect Password:=myPassword
 Next ws
 ActiveWorkbook.Protect Password:=myPassword
End Sub

Sub UnprotectAll()
 For Each ws In ActiveWorkbook.Worksheets
 ws.Unprotect Password:=myPassword
 Next ws
 ActiveWorkbook.Unprotect Password:=myPassword
End Sub

I'm not sure what you mean here:

when some one try to remove the password protection to the sheet and to the book, this code run and leave the file without work.

If someone tries to unprotect the workbook/sheets, the workbook closes without saving? Why not just set it as Read-Only for all users except you? If that would be acceptable, let me know and I'll post the relevant code.

Just note that Excel's protection is very weak and can be broken quickly by someone who wants in.

Hope that helps,

Smitty

rapidito78840 · Jul 20, 2005

Mr Penny:

Code:

I'm not sure what you mean here: 
--------------------------------------------------------------------------------

quote: 
--------------------------------------------------------------------------------
when some one try to remove the password protection to the sheet and to the book, this code run and leave the file without work

yes, the sheet and the book have a password, but if you run another program to unprotect the sheet and to unprotect the book, the excel file its closed and without saving nothing....

for last:
please ( maybe you are

) but when or how to put the code what you supply ? because I have 31 sheets on this file ?
thanks

rapidito78840 · Jul 20, 2005

Mr. Penny:

Code:

If someone tries to unprotect the workbook/sheets, the workbook closes without saving? Why not just set it as Read-Only for all users except you? If that would be acceptable,

the reazon to not do this , its : in some areas on the protected sheets, anybody can put information and work on them, but in some areas not, if I do "read only" the excel file its not work in all sheets...

Smitty · Jul 20, 2005

You can prevent the user from event attempting to unprotect with:

Sub DisableProtection()
Application.CommandBars("Tools").Controls.Item("Protection").Enabled = False
End Sub

Sub EnableProtection()
Application.CommandBars("Tools").Controls.Item("Protection").Enabled = True
End Sub

when or how to put the code what you supply ? because I have 31 sheets on this file ?

As for where to put the code, open the VB Editor (ALT+F11), then goto Insert-->Module and paste the code in the new window that opens on the right. You can call it from Tools-->Macro-->Macros or hit F5 from within any procedure. The first code will protect all of your worksheets regardless of how many there are.

Here is some code that will test for worksheet structure and if it's been unprotected it will close without saving (just read the comments):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 ' If this works the way that you want it you can change the Message Box
 ' to indicate that the user is bad and the workbook will close wihout saving changes
 ' Then uncomment the ActiveWorkbook.Close False lines
 If Not ActiveSheet.ProtectContents Then
 MsgBox "Sheet is not Protected", , "Protection Status"
 ' ActiveWorkbook.Close False
 End If
 If Not ActiveWorkbook.ProtectWindows Then
 MsgBox "Workbook Windows not Protected", , "Protection Status"
 ' ActiveWorkbook.Close False
 End If
 If Not ActiveWorkbook.ProtectStructure Then
 MsgBox "Workbook Structure is not Protected", , "Protection Status"
 ' ActiveWorkbook.Close False
 End If
End Sub


That particular piece of code goes in the ThisWorkbook module.

Smitty

EDIT: Note that if you're relying on code to keep people honest, then remove any additional temptation and protect the VBA project. In the VBE goto Tools-->VBA Project Properties-->Protection-->Lock Project for Viewing.

rapidito78840 · Jul 20, 2005

Mr Penny:
I need to put the two codes?

Code:

Sub DisableProtection()
    Application.CommandBars("Tools").Controls.Item("Protection").Enabled = False
End Sub

Sub EnableProtection()
    Application.CommandBars("Tools").Controls.Item("Protection").Enabled = True
End Sub

and this too ?

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    '   If this works the way that you want it you can change the Message Box
    '   to indicate that the user is bad and the workbook will close wihout saving changes
    '   Then uncomment the ActiveWorkbook.Close False lines
    If Not ActiveSheet.ProtectContents Then
        MsgBox "Sheet is not Protected", , "Protection Status"
        '   ActiveWorkbook.Close False
    End If
    If Not ActiveWorkbook.ProtectWindows Then
        MsgBox "Workbook Windows not Protected", , "Protection Status"
        '   ActiveWorkbook.Close False
    End If
    If Not ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook Structure is not Protected", , "Protection Status"
        '   ActiveWorkbook.Close False
    End If
End Sub

the second code its with the green letters ?

thanks

Smitty · Jul 21, 2005

Por favor, me llamo es "Smitty". "Mr. Penny" esta mi padre. :wink:

Regarding:

I need to put the two codes?

Not necessarily. The first snippet will disable the Protection option from the "Tools" menu bar; it's up to you to invoke that or not.

Regarding the second code snippet, it will alert you or a user if a workbook/sheet is not protected. The green parts are code comments; anything preceded by an apostrophe in VBA is ignored and viewed as a comment. Deleting the apostrophe activates the code (if it's valid code).

If you're having a hard time understanding what I'm (or anyone else) is posting, you may want to refer to this post in your post in the International Forum for translation.

I apoligize if my Spanish skills stink (yo hablo Espanol un poquito...I used to be pert good at West Texas Ranch Mexican though...).

There are many members who are competent in Spanish, unlike me.

My apologies if I confused you.

Smitty

rapidito78840 · Jul 21, 2005

Mr. Smitty :
no you dont confuse to me, but I put all the codes what you supply, and with the program "advanced Office password recovery professional edition" all the password in all the sheets are removed and the vba code ( locked) its be cleared.

did you have another option.... because I follow all what you say to insert the codes lock on VBA, but with this program all password are removed and you can change anything.

PLEASE ANOTHER CHOICE ?

THANKS and sorry for the name....

Smitty · Jul 21, 2005

advanced Office password recovery professional edition

If you're defeating Excel's protection features using an external program, then there's pretty much nothing you can do. Excel is not a secure environment, nor has it ever been marketed as one.

Smitty

need code VBA to protect an excel file

rapidito78840

Board Regular

Excel Facts

Smitty

Legend

rapidito78840

Board Regular

rapidito78840

Board Regular

Smitty

Legend

rapidito78840

Board Regular

Smitty

Legend

rapidito78840

Board Regular

Smitty

Legend

Similar threads

Forum statistics

Share this page

need code VBA to protect an excel file

Board Regular

Excel Facts

Legend

Board Regular

Board Regular

Legend

Board Regular

Legend

Board Regular

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock