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?



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:

Option Explicit

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

            GoTo NameExist

        End If



    ActiveWorkbook.Names.Add Name:="EV__WSINFO__", RefersToR1C1:="=" & strPassword

    ActiveWorkbook.Names("EV__WSINFO__").Visible = False


End Function

B.R. Vadim

1 View this answer in context