CODEWITHSUNDEEP
excelvba
Kevin P.
Kevin P.

Reputation: 1053

Open Comment (Editable) When Cell is Selected

I have a feeling this is not achievable. I would like to automate Right Clicking a cell and selecting Edit Comment by just clicking the cell. It appears that you cannot begin editing an object with VBA and not finish it.

I know it is silly but being able to just activate a cell and have the comment editable right then and there would be fantastic. Any options?

I have tried the following:

SHEET1:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("N19:N30")) Is Nothing Then
            Call Comment
        End If
    End If
End Sub

MODULE:

Sub Comment()
'Edit Comment upon cell selection
cmnt = ActiveCell.Comment.Text
Cell = ActiveCell.Address
    With Range(Cell)
       .Comment.Text Text:=cmnt
    End With
End Sub

However, this simply rewrites the comment and does not leave the object editable.

Upvotes: 0

Views: 74

Answers (1)

RADO
RADO

Reputation: 8148

I don't think what you are asking is possible exactly (VBA object "comment" does not have any methods for that).

However, you can imitate this functionality reasonaly well with InputBox. For example, if selection changes to a cell that has a comment, an input box will automatically popup with existing comment prepopulated and ready for edits. If selection changes to a cell without a comment, it will be ignored. Code:

Sub Edit_Comment(Current_Cell As Range)
Dim New_Comment As String

    If Current_Cell.Comment Is Nothing Then Exit Sub

    New_Comment = InputBox("", , Current_Cell.Comment.Text)
    If StrPtr(New_Comment) <> 0 Then Current_Cell.Comment.Text Text:=New_Comment

End Sub

To call this sub, replace in your code Call Comment with Call Edit_Comment(Target)

A couple of notes:

  • I would recomment not to call your subroutine "Comment" to avoid confusion with VBA object "Comment";
  • Instead of using "ActiveCell", pass "Target" as a parameter to the subroutine.

The way the sub works: It first checks if the cell has a comment. If not, it exits. If yes, it opens an input box and pre-populates it with an existing comment. User response is captured in "New_Comment" string and it overwrites old comment. The only tricky part is to handle situation when users hit "cancel" button in the input box. That's solved by If StrPtr(New_Comment) <> 0 test.

If you need input box to pop up for any cells regardless of whether they have comments or not, you can modify the above code:

Sub Edit_Comment(Current_Cell As Range)
Dim New_Comment As String

    If Current_Cell.Comment Is Nothing Then
        New_Comment = InputBox("")
        If StrPtr(New_Comment) <> 0 Then Current_Cell.AddComment Text:=New_Comment
    Else
        New_Comment = InputBox("", , Current_Cell.Comment.Text)
        If StrPtr(New_Comment) <> 0 Then Current_Cell.Comment.Text Text:=New_Comment
    End If

End Sub

It first tests if the commment exists; if yes, it edits it, otherwise it creates it.

Upvotes: 1

Related Questions