7 Replies Latest reply: Nov 25, 2014 2:15 PM by Vadim Kalinin RSS

set worksheet password in VBA Macro?

ali baba
Currently Being Moderated

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?

  • Re: set worksheet password in VBA Macro?
    Roberto Vidotti
    Currently Being Moderated

    Hi,

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

     

    Kind regards

         Roberto

    • Re: set worksheet password in VBA Macro?
      ali baba
      Currently Being Moderated

      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

       

      • Re: set worksheet password in VBA Macro?
        Vadim Kalinin
        Currently Being Moderated

        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

  • Re: set worksheet password in VBA Macro?
    Bruno Marcos
    Currently Being Moderated

    Hi,

     

    That VBA code may work in BPC 10?

     

    tks

Actions