C# VSTO Excel Determin Comment Selection-CodePudding

I have created a VSTO excel plug-in, which is supposed to store a comment text in a database each time the comment has been changed.

(I'm giving you the code example in VBA since usually this is where I test initial ideas first)

When a comment is selected TypeName(Selection) returns “TextBox”

so what I initially thought would work is to put it into Workbook_SheetSelectionChange:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  If TypeName(Target) = "TextBox" Then
    'let the user enter or change the comment
    'once the user has done that and changes the selection
    'trigger the save option to DB
  End If
End Sub

The problem is that selecting the comment does not trigger Workbook_SheetSelectionChange. Any suggestions on how to approach this problem is much appreciated.

CodePudding user response：

In VBA I'd check the cell comment content before and after leaving the cell as follows:

Option Explicit

Dim lastAddress As String
Dim cmtAddress As String
Dim cmtText As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If Not .Comment Is Nothing Then
            cmtAddress = .Address
            cmtText = .Comment.Text
        Else
            If cmtAddress <> vbNullString Then
                If Range(cmtAddress).Comment.Text <> cmtText Then
                    MsgBox "comment changed in cell '" & cmtAddress & "'"
                End If
                cmtAddress = vbNullString
            
            Else
                If Not Range(lastAddress).Comment Is Nothing Then
                    MsgBox "comment added in cell '" & lastAddress & "'"
                End If
            End If
        End If
        
        lastAddress = .Address
    End With
End Sub

CodePudding user response：

Please, try the next adapted code event, covering more situations. But **all credit must go to @user3598756, who had the brilliant idea. From the beginning I even did not understood your question...

Option Explicit

Dim lastAddress As String
Dim cmtAddress As String
Dim cmtText As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If Not .Comment Is Nothing Then
                If cmtAddress <> vbNullString Then
                    If Not Range(cmtAddress).Comment Is Nothing Then
                        If Range(cmtAddress).Comment.Text <> cmtText Then
                            MsgBox "Comment changed in cell '" & cmtAddress & "', " & vbCrLf & _
                            "(""" & Range(cmtAddress).Comment.Text & """ instead of """ & cmtText & """)."
                        End If
                    End If
                End If
                If Not lastAddress = "" Then
                        If Not Range(lastAddress).Comment Is Nothing Then
                            MsgBox "Comment added in cell '" & lastAddress & "'" & vbCrLf & _
                            "(""" & Range(lastAddress).Comment.Text & """)."
                        End If
                End If
                cmtAddress = .address
                cmtText = .Comment.Text
        Else
                If cmtAddress <> vbNullString Then
                    If Not Range(cmtAddress).Comment Is Nothing Then
                        If Range(cmtAddress).Comment.Text <> cmtText Then
                            MsgBox "Comment changed in cell '" & cmtAddress & "', " & vbCrLf & _
                            "(""" & Range(cmtAddress).Comment.Text & """ instead of """ & cmtText & """)."
                        End If
                    End If
                    cmtAddress = vbNullString
                Else
                   If Not lastAddress = "" Then
                        If Not Range(lastAddress).Comment Is Nothing Then
                            MsgBox "Comment added in cell '" & lastAddress & "'" & vbCrLf & _
                            "(""" & Range(lastAddress).Comment.Text & """)."
                        End If
                    End If
                End If
        End If
        
        lastAddress = .address
    End With
End Sub

The idea is that the event should also catch the cases of next selection having such a 'text box', too. And something to prevent the empty addresses when the process starts...