set worksheet password in VBA Macro?
i need to unprotect and protect worksheet in VBA macro. This must be done by BPC Set Worksheet Password capability.
Excel native protection can be managed in macro, but it is not suitable for us.
We won't use excel's native protection.
I made a macro to manage BPC Set Worksheet Password feature. I recorded a macro, but it doesn't work.
Is there any function to do this?
Vadim Kalinin replied
BPC Set Worksheet Password is actually doing the following:
1. For each worksheet in the workbook the standard Excel protection is set.
2. Password is stored in the Name object EV__WSINFO__ in order to be able to unprotect sheet when it's required to refresh data on this sheet.
VBA code is like this:
Public Function BPCProtect(strPassword As String)
Dim wshTemp As Worksheet
Dim namTemp As Name
For Each wshTemp In ActiveWorkbook
wshTemp.Protect Password:=strPassword, UserInterfaceOnly:=True
For Each namTemp In ActiveWorkbook.Names
If namTemp.Name = "EV__WSINFO__" Then
namTemp.RefersToR1C1 = "=" & strPassword
ActiveWorkbook.Names.Add Name:="EV__WSINFO__", RefersToR1C1:="=" & strPassword
ActiveWorkbook.Names("EV__WSINFO__").Visible = False