Tag Archives: excel

VBA EXCEL: Duplicate Comments to Matching Cells of Another Spreadsheet in the Same Workbook

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 😉