Comments in Excel

Comments in Excel

Comments

In Excel comments works like remark, note or hint. Consider you made a value change out of 1 Lac cells and still you want to draw attention from the reader to understand why value has been changed and of course when and by whom? the comment is a solution which sleeps in hidden mode inside a cell and upon hover the mouse pointer it pops up the tip written under comment as shown below:

Steps to insert a comment:

  • Select appropriate cell
  • Navigate to Review tab
  • Click New Comment under Comments group or right click on the selected cell and choose Insert Comment command from the popup as shown below in figure 1.1
  • A tip box will be opened where you can write your comment
figure 1.1

Edit comment:

  • Hover mouse over the cell containing comment or Click Show All Comments on the Review ribbon under Comments group
  • Select Edit Comment from Review tab under comments group or right click on the cell and choose Edit Comment commend as shown in the figure 1.2
  • Cursor goes inside comment tip box in editing mode where you can make necessary changes.
figure 1.2

Delete Comment

  • Select cell containing comment and choose Delete from Review tab under Comments group or right click on the cell and select Delete Comment command from the popup

Navigate: there are few more commands which I will explain one by one on the tool bar:

  1. Previous: Shows previous comment of current selection
  2. Next: Shows next comment of current selection
  3. Show/Hide Comment: toggles comment visibility on entire sheet
  4. Show All Comments: shows all comments on the sheet

Lets put code to achieve automation: In this article we are using Excel Addin technique which is built on VSTO (Visual Studio Tools for Office) technology hence following prerequisites are recommended:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example

private void btnCommentOperation_Click(object sender, RibbonControlEventArgs e)
{
	//Retain user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Add comment
	oRange.AddComment("This is VBAOVERALL comment");

	//Take current comment reference
	excel.Comment oComment = oRange.Comment;

	//toggle visibility
	if (oComment.Visible == false)
		oComment.Visible = true;
	else
		oComment.Visible = false;

	//display comment indicator
	excel.Application oApplication=Globals.ThisAddIn.Application;
	oApplication.DisplayCommentIndicator = excel.XlCommentDisplayMode.xlCommentAndIndicator;

	//Navigate comments
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//All comments in a sheet
	excel.Comments oComments = oWorksheet.Comments;
	
	//Next
	oComments.Item(1).Next();

	//Delete comment
	oComment.Delete();
}

VB.Net code example

Private Sub btnComments_Click(sender As Object, e As RibbonControlEventArgs) Handles btnComments.Click
	'Retain user selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Add comment
	oRange.AddComment("This is VBAOVERALL comment")

	'Take current comment reference
	dim oComment as excel.Comment
	oComment = oRange.Comment

	'toggle visibility
	If oComment.Visible=False Then
		oComment.Visible=True
	Else
		oComment.Visible=False
	End If

	'display comment indicator
	Dim oApplication as excel.Application
	oApplication =Globals.ThisAddIn.Application
	oApplication.DisplayCommentIndicator = excel.XlCommentDisplayMode.xlCommentAndIndicator
End Sub

VBA code example

Sub Comments()
    'Retains seleciton
    Dim oRange As Range
    Set oRange = Selection
    
    'Insert comment
    oRange.AddComment Text:="VBAOVERALL:" & Chr(10) & "This is a comment"
    
    'Retain comment object
    Dim oComment As Comment
    Set oComment = oRange.Comment
    
    'Check and toggle visibility
    If oComment.Visible = False Then
        oComment.Visible = True
    Else
        oComment.Visible = False
    End If
    
    'Show indicators
    Application.DisplayCommentIndicator = xlCommentAndIndicator
End Sub

VBA Copy/Paste Formatting

Public Sub CopyPasteCommentsWithFormatting()
    'Declare range object
    Dim oRange As Range
    
    'declare target object
    Dim oTargetObject As Range
    'bind target
    Set oTargetObject = Range("E1")
    
    'bind selection
    Set oRange = Selection
    
    'Copy range
    oRange.Copy
    
    'Paste comments with formatting
    oTargetObject.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    'Memory cleanup
    Set oRange = Nothing
    Set oTargetObject = Nothing
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next >> Allow Edit Ranges in Protected Sheet Excel

Leave a Reply

Your email address will not be published.