REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Description:

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

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
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

3 Comments

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

    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

  2. PNRao March 24, 2014 at 11:17 PM

    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!

  3. Sathish October 26, 2016 at 11:17 AM

    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