I have an Excel workbook with 13 protected sheets, January through December plus a Summary that needed the same cell comments for each month.
There were 2 options…
- Type comments into each sheet manually; then update 11 sheets (Feb-Dec) by hand whenever comments need changing 😬
- Add VBA code to the workbook that automatically duplicates cell comments from January (unprotected) to February through December (protected) when those sheets are activated 😎
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim xSheetProtected As Boolean 'Can add Case conditions for varying sheet passwords Select Case Sh.Index Case 1 'Unprotected xPassword = "" Case Else xPassword = "Pa$$w0rd" End Select If Sh.ProtectContents Then If xPassword = "" Then MsgBox "Missing password for protected sheet!", vbExclamation Exit Sub Else xSheetProtected = True On Error GoTo Invalid_Sheet_Password Sh.unprotect Password:=xPassword End If End If 'Copy January header cell comments to February-December If Sh.Index > 1 And Sh.Index < Application.Sheets.Count Then 'Columns containing comments (eg: row 3) For Each xCell In Worksheets(1).Range("3:3") If xCell.Text <> "" Then Range(xCell.Address).ClearComments If Not xCell.Comment Is Nothing Then Range(xCell.Address).AddComment xCell.Comment.Text End If Else 'Stop on empty cell GoTo Workbook_SheetActivate_End End If Next End If Workbook_SheetActivate_End: If xSheetProtected = True Then Sh.Protect Password:=xPassword End If Exit Sub Invalid_Sheet_Password: MsgBox "Password provided for """ & Sh.Name & """ sheet does not match!", vbExclamation, "Invalid Password" Exit Sub End Sub
If you got this far, I assume you can follow VBA code. That said, the code should be self-explanatory with my code comments are naming conventions 😉