VBA for Protecting Workbook

[James__S]

Hi,

Would anyone have VBA Code to lock a Workbook with 50+ tabs

1. Workbook can only be edited if correct password entered

2. If the person does not have the password then the Workbook is Read-Only

Thanks

 

[Michael M]

Maybe like this

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect "password"
Next ws
End Sub

 

[James__S]

Maybe like this

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect "password"
Next ws
End Sub

Hi

Thank you

This did not protect the workbook. I placed the VBA in a module

Thanks

 

[Michael M]

Sorry....I thought locking the sheets may have been adequate...

Try

Sub MM1()
Dim pwd As String
pwd = InputBox("Please Enter the password")
If pwd = "" Then Exit Sub
ActiveWorkbook.Protect Structure:=True, Windows:=True, Password:=pwd
MsgBox "Workbook is now locked."
End Sub

 

[James__S]

Sorry....I thought locking the sheets may have been adequate...

Try

Sub MM1()
Dim pwd As String
pwd = InputBox("Please Enter the password")
If pwd = "" Then Exit Sub
ActiveWorkbook.Protect Structure:=True, Windows:=True, Password:=pwd
MsgBox "Workbook is now locked."
End Sub

Hi michael

I entered the VBA into Workbook module but it still does not lock the workbook.

I ran the Code from Macros box and it opened up Password Box. i entered Password and locked. When i opened back up i could type in any cell

 

[Michael M]

Protect a workbook will prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets
Protecting each sheet stops the user from entering deleting data...hence my first code !

 

[James__S]

Thank you

Yes correct the VBA code below:

Is it possible with the code for the following
1. When the workbook is opened all sheets are locked and content read-only
2. When a person clicks on the cell in any sheet in the workbook a Pop-Up Message Box asks for a password to unlock. If password is incorrect than again workbook/worksheets are read-only
3. Workbook is automatically locked when closed
4. Can password be in code as this then can be changed if required

Should code be placed in module or Thisworkbook module

Thanks

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect "password"
Next ws
End Sub

 

[Michael M]

If you want sheets to be unprotected / protected seperately you will need to place a code in each worksheet !

Sub MM1()
Dim pwd As String
pwd = InputBox("Please Enter the password")
If pwd = "" Then Exit Sub
ActiveSheet.Unprotect Password:=pwd
MsgBox "Worksheet is now open."
End Sub

You would then need a workbook close event to reprotect ALL sheets

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect "password"
Next ws
End Sub