set worksheet password in VBA Macro?

[Former Member]

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?

[former_member186498 · Accepted solution]

Hi,

no it should work, can you please post your VBA code?

Kind regards

     Roberto

[Former Member · Accepted solution]

Roberto Vidotti wrote:
Hi,
no it should work, can you please post your VBA code?
Kind regards
     Roberto

Sub Makro1()
'
' Makro1 Makro
'
'
    ActiveWorkbook.Names.Add Name:="EV__WSINFO__", RefersToR1C1:="=1"
    ActiveWorkbook.Names.Add Name:="EV__WBEVMODE__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__WBREFOPTIONS__", RefersToR1C1:= _
        "=134217735"
    ActiveWorkbook.Names.Add Name:="EV__WSINFO__", RefersToR1C1:="=1"
    ActiveWorkbook.Names.Add Name:="EV__MAXEXPCOLS__", RefersToR1C1:="=100"
    ActiveWorkbook.Names.Add Name:="EV__MAXEXPROWS__", RefersToR1C1:="=1000"
    ActiveWorkbook.Names.Add Name:="EV__WBVERSION__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__EXPOPTIONS__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__MEMORYCVW__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__EVCOM_OPTIONS__", RefersToR1C1:="=8"
    ActiveWorkbook.Names("EV__WSINFO__").Delete
    ActiveWorkbook.Names("EV__WSINFO__").Delete
    ActiveWorkbook.Names.Add Name:="EV__WBEVMODE__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__WBREFOPTIONS__", RefersToR1C1:= _
        "=134217735"
    ActiveWorkbook.Names.Add Name:="EV__MAXEXPCOLS__", RefersToR1C1:="=100"
    ActiveWorkbook.Names.Add Name:="EV__MAXEXPROWS__", RefersToR1C1:="=1000"
    ActiveWorkbook.Names.Add Name:="EV__WBVERSION__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__EXPOPTIONS__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__MEMORYCVW__", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="EV__EVCOM_OPTIONS__", RefersToR1C1:="=8"
End Sub

[former_member186338 · Accepted solution]

Hi

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
    Next
    For Each namTemp In ActiveWorkbook.Names
        If namTemp.Name = "EV__WSINFO__" Then
            namTemp.RefersToR1C1 = "=" & strPassword
            GoTo NameExist
        End If
    Next
    ActiveWorkbook.Names.Add Name:="EV__WSINFO__", RefersToR1C1:="=" & strPassword
    ActiveWorkbook.Names("EV__WSINFO__").Visible = False
NameExist:
End Function

B.R. Vadim

[Former Member · Accepted solution]

thanks so much.

[former_member228447]

Hi,

That VBA code may work in BPC 10?

tks

[former_member186338]

Hi Bruno,

No for BPC 10 look on API SetSheetOption

Vadim