More discussions in SAP Planning and Consolidation, version for SAP NetWeaverWhere is place located?
    4 Replies Latest reply: Apr 22, 2012 1:42 PM by ali baba RSS

    set worksheet password in VBA Macro?

    ali baba
    ali baba Apr 18, 2012 9:48 AM
    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?

    • 1181 Views
      • Products: SAP Business Planning and Consolidation
    • Re: set worksheet password in VBA Macro?
      Roberto Vidotti
      Roberto Vidotti Apr 19, 2012 6:56 PM (in response to ali baba)
      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
        ali baba Apr 21, 2012 1:15 AM (in response to Roberto Vidotti)
        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
          Vadim Kalinin Apr 21, 2012 10:17 AM (in response to ali baba)
          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

    Actions