• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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