[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Excel Macro – Protects Sheets (allowing adding comments)

I have the attached code to protect and unprotect all the sheets at the click of a button in an excel workbook but I would like to allow the cells to have comments attached to them when the protection code is run but the current protection settings do not allow this.

I've had a little play but with no success.
Sub Protect_sheets()
 
    Dim wSheet          As Worksheet
    Dim Pwd             As String
 
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd

    Next wSheet
 
End Sub


Sub Unprotect_sheets()
 
    Dim wSheet          As Worksheet
    Dim Pwd             As String
 
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
    If Err <> 0 Then
        MsgBox "You have entered an incorect password. All worksheets could not " & _
        "be unprotected.", vbCritical, "Incorect Password"
    End If
    On Error GoTo 0
 
End Sub

Open in new window

0
Glyn Merritt
Asked:
Glyn Merritt
  • 3
  • 2
1 Solution
 
royhsiaoCommented:
Have you tried to allow all users of this worksheet to "edit objects"?

You could insert text box if you allow user to edit objects.
0
 
royhsiaoCommented:
You can insert comment as well.
Edit-Object.JPG
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
I have no problem doing it manually but I have several workbooks with many sheets so if I do it manually it will take me a long while. I would like to incorporate this into the code attached to the question.
0
 
royhsiaoCommented:
No problem
Sub Protect_sheets()
 
    Dim wSheet          As Worksheet
    Dim Pwd             As String
 
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd, DrawingObjects:=False, Contents:=True, Scenarios:=True

    Next wSheet
 
End Sub


Sub Unprotect_sheets()
 
    Dim wSheet          As Worksheet
    Dim Pwd             As String
 
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
    If Err <> 0 Then
        MsgBox "You have entered an incorect password. All worksheets could not " & _
        "be unprotected.", vbCritical, "Incorect Password"
    End If
    On Error GoTo 0
 
End Sub

Open in new window

0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Spot on thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now