Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Macros to Protect/Unprotect Worksheets and Workbooks

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Hi Buzzy,

    Welcome to board !!

    Please edit your post and add code tags. [code] your code here [/ code] (without space)
    Kris

    ExcelFox

    Comment


    • #17
      Re: Macros to Protect/Unprotect Worksheets and Workbooks

      Shouldn't ask questions in this Forum
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #18
        Re: Macros to Protect/Unprotect Worksheets and Workbooks

        Hey Roy,

        Welcome back !!
        Kris

        ExcelFox

        Comment


        • #19
          Re: Macros to Protect/Unprotect Worksheets and Workbooks

          Hi Kris

          Thanks for the birthday greeting yesterday
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment


          • #20
            Re: Macros to Protect/Unprotect Worksheets and Workbooks

            Luttrrt----

            Just wanted to say thanks for the VBA macros. I am doing project work at my new job, so I am giving the existing spreadsheets a complete facelift and then automating almost everything. Before (I arrived), formulas were constantly getting messed up due to an innocent error by the end user. You macros allow me an easy way to protect them, and then unprotect them when I need to make adjustments. Thanks again, these scripts are awesome!
            Sherry
            Orlando, FL

            Comment


            • #21
              Re: Macros to Protect/Unprotect Worksheets and Workbooks

              VB:
              Code:
              Dim ws As Worksheet
              Sub ProtectAll()
              Dim S As Object
              Dim pWord1 As String, pWord2 As String
              
              pWord1 = InputBox("Please Enter the password")
              
              If pWord1 = "" Then Exit Sub
                 pWord2 = InputBox("Please re-enter the password")
              If pWord2 = "" Then Exit Sub
                 'make certain passwords are identical
              If InStr(1, pWord2, pWord1, 0) = 0 Or _ 
                 InStr(1, pWord1, pWord2, 0) = 0 Then
              MsgBox "You entered different passwords. No action taken"
              Exit Sub
              
              End If
              For Each ws In Worksheets
              ws.Protect Password:=pWord1
              Sheets("Sheet2").Unprotect Password:=pWord1
              Next
              MsgBox "All sheets Protected."
              Exit Sub
              End Sub
              I need a revise code for these current code that i posted, since i cannot work into it..

              my problem is, everytime i put a password on the protectall, it overwrites the previous password i already put it in.
              for example, i put 12345 password for the first time and it did protect the whole sheet, but when i try to input another password for example 67890, it overwrites the 12345??

              thanks for the help..

              Comment


              • #22
                Re: Macros to Protect/Unprotect Worksheets and Workbooks

                Hello,
                I have seen this issue come up a few times on multiple forums and asked a multitude of diferent ways.
                It was a very frustrating problem and I hope this can be useful to anyone.

                Here is reletively simple code that I have pulled together form many sources and then customizing it.
                This code will use a command button to unprotect all worksheets within a workbook and notify the user of the state of the worksheets.

                Code:
                Private Sub cmdGuardControl_Click()
                '<<<<<<<<<<< Protects/Unprotects Worksheets >>>>>>>>>>>>>
                '<<<<<< Controls Color & Caption of command button >>>>>>
                '<<<< Color & Caption indicates worksheets condition >>>>
                
                Dim wSheet As Worksheet
                Application.ScreenUpdating = False
                        For Each wSheet In Worksheets
                
                                        '<<<<<<<<<<< Unprotects Worksheets
                                If wSheet.ProtectContents = True Then
                                        wSheet.Unprotect Password:="PassWord1"
                                   
                                        '<<<<<<<<<<< Protects Worksheets
                                ElseIf wSheet.ProtectContents = False Then
                                            wSheet.Protect Password:="PassWord1"
                                End If
                 
                        Next wSheet
                        
                        
                        
                                If ActiveSheet.ProtectContents = True Then
                                    
                                        '<<<<<<<<<<< Controls Color and Caption of cmdGuardControl (Button)
                                            ActiveSheet.Unprotect Password:="PassWord1"
                                        cmdGuardControl.Caption = "Protected"
                                                Me.cmdGuardControl.BackColor = RGB(50, 100, 50)
                                                Me.cmdGuardControl.ForeColor = RGB(0, 225, 0)
                                            ActiveSheet.Protect Password:="PassWord1"
                        
                        
                        
                                ElseIf ActiveSheet.ProtectContents = False Then
                                        
                                        '<<<<<<<<<<< Controls Color and Caption of cmdGuardControl (Button)
                                        cmdGuardControl.Caption = "Unprotected"
                                            Me.cmdGuardControl.BackColor = RGB(225, 25, 25)
                                            Me.cmdGuardControl.ForeColor = RGB(50, 0, 0)
                                End If
                Application.ScreenUpdating = True
                 
                
                End Sub

                The password can be changed to whatever personal choice you would like.
                For general protection without a password, simply leave the password as, =""

                For any cells that need to remain unprotected, simply uncheck the "locked" box in "format cells", "protection".

                I have found that hiding a worksheet with this button then protecting the workbook keeps things pretty tight.
                Also PW protect your code.





                I hope this helps,
                WQ

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X