VSTO Excel插件：批注变更存库的触发事件问题

1. 使用Shape选择事件监控批注选中 #

Workbook_SheetSelectionChange仅响应单元格选区变化，选中批注（文本框）属于Shape操作，需改用Workbook_SheetShapeSelectionChange事件：

Private prevCommentText As String
Private prevCommentCell As String

Private Sub Workbook_SheetShapeSelectionChange(ByVal Sh As Object, ByVal SelectedShapes As Excel.ShapeRange)
    If SelectedShapes.Count = 1 Then
        Dim shp As Shape
        Set shp = SelectedShapes(1)
        
        ' 判断是否为批注形状（旧版Excel中批注Shape类型为msoComment）
        If shp.Type = msoComment Then
            Dim targetComment As Comment
            Set targetComment = shp.OLEFormat.Object
            ' 记录当前批注内容和所属单元格，用于后续对比变更
            prevCommentText = targetComment.Text
            prevCommentCell = targetComment.Parent.Address(External:=True)
        End If
    End If
End Sub

' 当切换回单元格选区时，检查批注是否已变更
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If prevCommentCell <> "" Then
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets(Split(prevCommentCell, "!")(0))
        Dim cell As Range
        Set cell = ws.Range(Split(prevCommentCell, "!")(1))
        
        If cell.Comment Is Nothing Then Exit Sub
        ' 对比内容，若变更则执行保存
        If cell.Comment.Text <> prevCommentText Then
            SaveCommentToDatabase prevCommentCell, cell.Comment.Text
            ' 重置记录变量
            prevCommentText = ""
            prevCommentCell = ""
        End If
    End If
End Sub

' 数据库保存示例函数
Private Sub SaveCommentToDatabase(cellAddr As String, commentText As String)
    ' 此处编写数据库存储逻辑，例如使用ADO连接执行插入/更新
    ' 示例：
    ' Dim conn As New ADODB.Connection
    ' conn.Open "你的数据库连接字符串"
    ' conn.Execute "INSERT INTO CommentLogs (CellAddress, CommentText, UpdateTime) VALUES ('" & cellAddr & "', '" & commentText & "', NOW())"
    ' conn.Close
End Sub

2. 绑定批注的TextChanged事件（更精准） #

VBA本身没有内置的批注变更事件，可通过类模块实现事件绑定，直接捕获批注内容修改动作：

步骤1：新建类模块

插入类模块，命名为clsCommentWatcher，写入以下代码：

Public WithEvents xlComment As Comment

Private Sub xlComment_TextChanged(ByVal Text As String)
    ' 批注内容变更时直接触发保存
    SaveCommentToDatabase xlComment.Parent.Address(External:=True), Text
End Sub

步骤2：在ThisWorkbook中初始化事件绑定

Private commentWatchers As Collection

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim cmt As Comment
    Dim watcher As clsCommentWatcher
    
    Set commentWatchers = New Collection
    
    ' 遍历现有批注绑定事件
    For Each ws In ThisWorkbook.Worksheets
        For Each cmt In ws.Comments
            Set watcher = New clsCommentWatcher
            Set watcher.xlComment = cmt
            commentWatchers.Add watcher
        Next cmt
    Next ws
End Sub

' 处理新增批注的情况（可选）
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    ' 若双击单元格时无批注，创建后绑定事件
    If Target.Comment Is Nothing Then
        Target.AddComment
        Dim watcher As clsCommentWatcher
        Set watcher = New clsCommentWatcher
        Set watcher.xlComment = Target.Comment
        commentWatchers.Add watcher
    End If
End Sub

注意事项 #

• SaveCommentToDatabase需根据你的数据库类型（SQL Server/Access等）实现具体的连接和写入逻辑。
• 若使用新版Excel的"现代批注"，Shape类型判断可能需要调整，可通过shp.Name是否包含"Comment"关键字辅助判断。

内容的提问来源于stack exchange，提问作者Pete