Add Clear Comments in Excel VBA

Home/Excel VBA/Add Clear Comments in Excel VBA

Description:

Comments are helpful when you want to show some remarks or comments on particular cell, you can Add Clear Comments in Excel VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Add Clear Comments in Excel VBA – Solution:

You can use AddComment and ClearComments methods to do this.

Example:

Add Clear Comments in Excel VBA
The following code will show you how to add and clear comments using Excel VBA.

Code:
Sub sbAddComment()
'Deletes Existing Comments
Range("A3").ClearComments
'Creates Comment
Range("A3").AddComment
Range("A3").Comment.Text Text:="This is Example Comment Text"
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it
Practical Applications:

If you have lots of Cells to add comments, we general write the comments in another set of range and add using VBA.

Following is the Example program to add the comments from a range.

Sub sbAddComment_Example()
For iCntr = 1 To 30
'Clear if any existing comments
Range("A3").ClearComments
'Add a Comment from Column B
Range("A" & iCntr).AddComment
Range("A" & iCntr).Comment.Text Text:=Range("B" & iCntr).Value
Next iCntr
End Sub
Explantion:
  1. For Loop is to iterate from 1 to 30 rows, you can change as per your require mt
  2. ClearComents method is using to clear the existing comments if any
  3. AddComments method will add the comment in the particular range
  4. Comment.Text property is for adding the Commet text or message which you want ot show it the user on mose hover on a range
Instructions:
  1. Open an excel workbook
  2. Enter some data in Column A and B as per your requirement, to execute the above program you need to enter some data from Range A1 to Range B30.
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute it
LIMITED TIME OFFER
By |June 8th, 2013|Excel VBA|3 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

3 Comments

  1. Suganthi March 24, 2014 at 8:50 AM - Reply

    Hi,
    In this code,
    Sub sbAddComment_Example()
    For iCntr = 1 To 30
    ‘Clear if any existing comments
    Range(“A3”).ClearComments

    ‘Add a Comment from Column B
    Range(“A” & iCntr).AddComment
    Range(“A” & iCntr).Comment.Text Text:=Range(“B” & iCntr).Value

    Next iCntr
    End Sub

    ” Range(“A” & iCntr).Comment.Text Text:=Range(“B” & iCntr).Value” code is not working. Getting an error “Application Defined or Object Defined error” Runtime error 1004. Kindly give me correct code for this.

    Thanks,
    Suganthi

    • PNRao March 24, 2014 at 11:17 PM - Reply

      Hi,
      Type some text in Column B1:B30 and try this code:

      Sub sbAddComment_Example()
      For iCntr = 1 To 30
      'Clear if any existing comments
      Range("A3").ClearComments

      'Add a Comment from Column B
      Range("A" & iCntr).AddComment
      Range("A" & iCntr).Comment.Text Text:=Range("B" & iCntr).Value

      Next iCntr
      End Sub

      Thanks-PNRao!

  2. Sathish October 26, 2016 at 11:17 AM - Reply

    Plese try this one

    Sub sbAddComment_Example()
    For iCntr = 1 To 30
    ‘Clear if any existing comments

    Clear A1 to A30 Clearcomments.

    Range(“A” & iCntr).ClearComments

    ‘Add a Comment from Column B
    Range(“A” & iCntr).AddComment
    Range(“A” & iCntr).Comment.Text Text:=Range(“B” & iCntr).Value

    Next iCntr
    End Sub

Leave A Comment


Related pages


insert cells in excelexcel calculate weeks between two datesworkbooks.close vbaexcel project management dashboard templatemerging cell in excelexcel duplicate checkvba create classexcel macro programming pdfdelete duplicate values excelshortcut key insert row excelvba current directoryvlookup in excel step by stepvba code for loopexcel macro to delete blank rowsexcel vba usernamehow to unhide column in excelsql query examples with answers pdfvba mergeexcel vba table lookupexcel offset functionactivesheet listobjectssales dashboard excel templateexcel vba report generatorcall function in vbavba listboxsas macro programming for beginnersrefresh pivot table macrovlookup excel vbafree excel calendar 2014how to password protect excel sheetend xlup rowexcel spreadsheet passwordvba excel substitutelistbox codewhat are ddl statements3d graph in excelvba excel 2007 tutorialsave xls as csvmicrosoft estimate templatehow to increase the column width in excelupdate access table from excel vbahow to create pivot in excelexcel formulas sumifpivot table tutorial excel 2013excel workbook tabsexcel vba call subexcel cell blankhow many chart types does excel offervba sort columnconditional function in excelsumif formula in excelproject budget template excelvbscript to delete fileexcel macro valuevariables in vbavlookup range lookupexcel connect to mysqlloop excel vbaexcel formulas 2007 with examplesvlookup templateexcel hyperlink to websitefileexists vbscriptinteger in vbacount function in ms excelsample project tracking spreadsheetaccess vba tutorialspastespecial xlpastevaluestick on excel spreadsheetduplicates in excel 2003excel codes and formulasvba collection excelexcel macro formatexcel delete duplicate rowsvba if cell containshow to break password of excel sheetexcel remove duplicates in columnhighlight duplicates excel