Comment Box is left open...need to close!

[salmerin]

I have this code that I got from another site, however, this code allows the user to enter a comment without the UserInput window, the only problem is that it leaves the comment window open and it won't close when the user selects another cell.

Sub CommentAddOrEdit()
'method suggested by Jon Peltier 2006-03-04
'adds new plain text comment or adds text
'at end of existing comment text
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    Set cmt = ActiveCell.AddComment
    cmt.text text:=""
  End If
  
  'type to add comment text to selected shape
  cmt.Visible = True
  cmt.Shape.Select

End Sub

How do I modify this so that when the user is done with the comment, and the user selects another cell to get the focus out of the comment box, the box will close?

 

[mikerickson]

try setting the .Visible property of the comment to False at the end of your routine.

 

[VoG]

Try

cmt.Visible = False

and delete

cmt.Shape.Select

 

[mikerickson]

I think that the comment has to be selected. It looks like the code is using that Selection to invoke the normal comment edit box.

One of my thoughts is that the sub is unneeded. I don't see the advantage of pressing a button to invoke that macro over just right clicking on the cell and selecting Edit co

 

[salmerin]

I think that the comment has to be selected. It looks like the code is using that Selection to invoke the normal comment edit box.

One of my thoughts is that the sub is unneeded. I don't see the advantage of pressing a button to invoke that macro over just right clicking on the cell and selecting Edit co

The problem is that the whole workbook is password protected, and in order to run other macros on any given sheet the (ActiveSheet.Protect Password:="Some password here") gets executed in order to make changes. So, the right click part you are talking about does not work as the Add | Edit | Delete Comment options aren't shown.

This is the reason for the Comment Sub I was inquiring about by using the normal edit window but can't get it to work properly as it remains open....and if I select .Visible to False then it won't open to add comments.

 

[mikerickson]

I don't know what version you are using, but Excel 2007 or after you can allow the user to "Edit Objects" on a protected sheet (Review tab>Protect sheet is how to get to this control).

Once that is set, the user will not be able to change a cell's contentets, but right clicking on a cell will give them the option to insert or edit comments.

 

[salmerin]

I don't know what version you are using, but Excel 2007 or after you can allow the user to "Edit Objects" on a protected sheet (Review tab>Protect sheet is how to get to this control).

Once that is set, the user will not be able to change a cell's contentets, but right clicking on a cell will give them the option to insert or edit comments.

The sheets (workbook) is password protected using VBA therefore I need a way to allow users to "Edit Objects" using vba.

If I use something similar to this:

.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True

But instead of "DrawingObjects", could I use something about EditObjects?

 

[mikerickson]

The setting that I mentioned can't be edited from VBA (as far as I can see).

But if you make that setting manualy, it will stick with the workbook and allow comment editing when the sheet is protected with VBA.

 

[salmerin]

The setting that I mentioned can't be edited from VBA (as far as I can see).

But if you make that setting manualy, it will stick with the workbook and allow comment editing when the sheet is protected with VBA.

Thanks, I'll give it a go!
Appreciate everyone's help in here!

 

[salmerin]

The setting that I mentioned can't be edited from VBA (as far as I can see).

But if you make that setting manualy, it will stick with the workbook and allow comment editing when the sheet is protected with VBA.

Thanks, I'll give it a go!
Appreciate everyone's help in here!