Multiple workbooks

Hi Sara,

Thank you for the forum question.

As I said on the course, you will need VBA codes to protect many sheets.

The code below will protect all sheets in the workbook with the same password. When you execute the code you will be prompt for a password.


Sub ProtectAll()

Dim wSheet As Worksheet

Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")

For Each wSheet In Worksheets

wSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _

AllowFormattingColumns:=True, AllowFormattingRows:=True

Next wSheet

End Sub


If you want to unprotect all sheets you can use the code below.

Sub UnProtectAll()

Dim wSheet As Worksheet

Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")

On Error Resume Next

For Each wSheet In Worksheets

wSheet.Unprotect Password:=Pwd

Next wSheet

If Err <> 0 Then

MsgBox "You have entered an incorrect password. All worksheets could not " & _

"be unprotected.", vbCritical, "Incorrect Password"

End If

On Error GoTo 0

End Sub



I do not know if you have any knowledge of adding the code to the file.

The link below will show you how to get the developer tab in Excel.

https://www.youtube.com/watch?v=JLQ8OuW0FlY

The link below will show you how to create a module in Excel from the deloper tab where you need to paste the code above.

https://www.youtube.com/watch?v=IIuKCxPlaTY



When you have added the code you must save the workbook as a macro enable workbook (.xlsm instead of .xlsx)

I am not sure what you mean by "and Data Manipulation". Please be more specific and I will be happy to help you.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector