Saturday, February 14, 2009

Excel VBA: 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
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", _
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
End Sub

Related posts:

1 comment:

  1. If you have this situation,then I recommend to use this application-Microsoft Excel the file error: there is a possibility that data is lost.,which in my view one of the best in this sphere,it has free status,also software is used to process important information, such as graphics, diagrams, commercial and statistical data,recover *.xlsx, *.xlsm, *.xltm, *.xltx or *.xlam formats,tool will help you if you have to repair Excel file error message: Excel error the file is not in a recognizable format,allows choosing a file, that shown an error like: Excel error message this file is not in a recognizable format, then, you should fix Excel file for errors and proceed with its analysis,attempts to recover your document with this message: Excel error and this file is not in a recognizable format and shows a preview window.