How to lock/unlock a worksheet/workbook with a defined password using a macro in excel?

philosopher76
philosopher76 used Ask the Experts™
on
I would like to save time opening and closing my file and I would like to run a macro to do so.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Group Finance Manager
Commented:
You would need to call the macro from another workbook.

Something like this


Sub OpenWb()
    Dim ws As Worksheet
    Workbooks.Open Filename:="C:\YourWorkBookName.xlsx", Password:="YourPasswprd"
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect "YourPassworsd"
    Next ws
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
To re-protect the sheets when closing place this code in the WorkBook close event

Option Explicit

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

End Sub

Open in new window


The workbook will still have it's protection unless you remove it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial