Disable the comments on the shortcut menu

bandit_1981

Board Regular
Joined
Aug 17, 2005
Messages
201
Hello all title says it all. I need to gray them out so no one can use comments. I can gray out the insert but where the edit and delete are not enabled at that time i cant figure out how to gray them out.


Code:
    With CommandBars("Cell")
        .Controls("Insert Comment").Enabled = False
        .Controls("Edit Comment").Enabled = False
        .Controls("Delete Comment").Enabled = False
    End With

This only works for the insert. How can i disable all 3?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
Hi,

If you cannot Insert comment how can you Edit it? So what you can do is, first delete all the comments, and then run your code, and add an errorhandler which will make the Sub exit.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> errorhandler
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Comments
    c.Delete
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">With</SPAN> CommandBars("Cell")
    .Controls("Delete Comment").Enabled = <SPAN style="color:#00007F">False</SPAN>
     .Controls("Insert Comment").Enabled = <SPAN style="color:#00007F">False</SPAN>
    .Controls("Edit Comment").Enabled = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
errorhandler:
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,118,389
Messages
5,571,850
Members
412,421
Latest member
Rimo86
Top