Open a cell comment for editing

[mark hansen]

I have a process where I need the user to enter information into a cell, then add a comment. I can add a comment with default text, but the user needs to change the default comments every time.

I use the following to add the comment:

ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:="Default text" & Chr(10) & ""

What I would like to do is, using VBA right click on the Cell and select "Edit comment"

I tried something silly as ActiveCell.Comment.Edit, but of course that doesn't work

Can this be done, or should I settle on the users needing to right click in the cell and select "Edit Comment"?

 

[yky]

What happens if you do this?

[COLOR=#333333]ActiveCell.AddComment[/COLOR]
[COLOR=#333333]ActiveCell.Comment.Text Text:="Default text" & Chr(10) & ""
[/COLOR][COLOR=#333333]ActiveCell.Comment.Visible = True[/COLOR]

 

[Matt Rogers]

Hi.

Double-clicking in the cell opens the comment-box.

 

[mark hansen]

Thanks for the help yky and Matt.

Making the comment visible (TRUE) will display it, but it remains up after editing. Thanks for the tip about double clicking. I found the SendKeys "+{F2}" options and it will work. I like to stay away from SendKeys when I can, but can't find anything else.

Thanks for help.
Mark

 

[FormR]

Hi, how about using an input box to capture the default comment text - for example:

Dim s As String
s = InputBox("Enter your default text..")
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=s & Chr(10) & ""

 

[yky]

Making the comment visible (TRUE) will display it, but it remains up after editing.

Then, put "ActiveCell.Comment.Visible = False" in a Worksheet event such as SelectionChange or BeforeDoubleClick.