Saturday, February 14, 2009

Excel VBA Macro Example: Protect & Unprotect Worksheet/Excel File

Imagine this; you are working with protected excel file, so only authorized user can access the file. Let's say you are the authorized user, and you are going to create macro excel that need an access to the protected file.

This vba macro example, sets password to excel file named "excel.xls". This example assumes excel file named "excel.xls" already open.

Sub ProtectExcel()
'write password
Workbooks("excel.xls").Password = "macroexcel"

'save workbook
Workbooks("excel.xls").Save
End Sub

This example opens the protected excel file named "excel.xls" on the D:\ drive.

Sub OpenProtectedExcel()
'open protected excel file
Workbooks.Open Filename:="D:\excel.xls", _
Password:="macroexcel"
End Sub

This example remove protection from worksheet named "Sheet1" and protect it with new password.

Sub xProtect()
'Assumes excel.xls already open
'unprotect sheet
Workbooks("excel.xls").Sheets("Sheet1").Unprotect "password"

'protect sheet with new password
Workbooks("excel.xls").Sheets("Sheet1").Protect "vbamacro"

'save changes
Workbooks("excel.xls").Save
End Sub

Related posts: