This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that is protected by setting password, how can I do it in Excel 2013 or Excel 2016 or Excel 365.
This post will show you two methods to achieve the result of inserting comments in a protected worksheet in Excel. The First method is to check the Edit Objects option while you are protecting your worksheet. And the second method is using VBA code to insert comments.
Insert Comments in Protected worksheet by checking Edit Objects Option
If you want to insert a comment for a given cell in your current worksheet that is protected in Excel, and you can use the most easily way is that checking the Edit Objects option when you are protecting your worksheet. Just do the following steps:
Step1: go to Review Tab and click Protect Sheet button under the Protect group.
Step2: the Protect Sheet dialog would be appeared, and you need to enter password to protect your worksheet and contents of locked cells in password box of Password to unprotect sheet. And then you need to check the Edit Objects option in the Allow all users of this worksheet to list box. Click Ok button.
Step3: reenter password to proceed in the confirm password dialog box. Click ok button.
Step4: you can try to insert a command in any one cell in your protected worksheet, and you would see that you can insert comments into one cell successfully now.
Insert Comments in Protected Worksheet using VBA Code
You can also use an Excel VBA Macro to achieve the same result of inserting comments in a protected worksheet in Excel. just do the following steps:
Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.
Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.
Step 2: In Microsoft Visual Basic for Applications window, enter below code:
Public Sub InsertCommentInProtectedSheet() Dim mypassword As String Dim myComment As String mypassword = "test" myTitle = "Insert comments in protected worksheet" myComment = Application.InputBox("please input one comment", myTitle, "", Type:=2) Application.ActiveSheet.Unprotect Password:=mypassword Application.ActiveCell.AddComment Application.ActiveCell.Comment.Text Text:=myComment ActiveSheet.Protect Password:=mypassword End Sub
Step 3: Save code, quit Microsoft Visual Basic for Applications.
Step 4: Click Developer->Macros to run Macro.
Step 5: Select the Macro Name ‘InsertCommentInProtectedSheet’ from the Macro window and click Run.
Step 6: you need to insert one comment for the current active cell, then clicking Ok button.
Step7: you should see that your comments has been inserted into the active cell in your worksheet that is proteced .
Note: if you want to use this vba code, and you need to change the password as yours.