In this article, we are going to learn how to use comment option in Microsoft Excel through VBA.

Comment is the source by which we can convey the message to every user if it is required to provide information about any particular cell.

We will learn:-

How to Insert Comment?

How to Delete All Comments?

How to Delete All Comments from the all Sheets to a Workbook?

How to Hide Comments Partially?

How to Hide Comments Completely?

How to show a Single Comment?

How to Visible or Show all of the Comments within an Entire Excel workbook?

Hide Specific Comments in Excel- Comments will still display.

How to Add Background Photos/Images Comment in Excel?

Now start with how to insert comment in Excel sheet?

Let’s take an example to understand how we can insert comments in Excel sheet.

We have data in sheet 2 in which we have In and Out time of every employee and also we have recorded working hours, Overtime hours and regular hours in the sheet. In some cells, we want to insert the comments.

Code Explanation:- First we need to select the subject name, then we will define the variables and then we will define all the ranges where we want to put the comments.
To run the macro, press the F5 key on your keyboard. All comments will get updated in the Excel sheet. You can identify the comments with the red rectangle in the corner of the cells.

How to delete all comments from a sheet?

Let’s take an example to understand how we can delete all the comments in Excel sheet.

We have data in sheet 2 in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet. In Excel, we have some comments which we want to delete.

To delete all the comments in sheet, follow below given steps:-

Open VBA Page and press the key Alt+F11.

Insert a module.

Write the below mentioned code:

Sub DeleteComment()
Cells.ClearComments
End Sub

How to delete all comments from all the cells in a workbook?

Let’s take an example to understand how we can delete all the comments in Excel sheet.

We have data in two sheets in which we have In and Out time of every employee and also, we have recorded working hours, overtime hours and regular hours in the sheet. In Excel, we have some comments which we want to delete, not only from the active sheet even from the workbook.

To delete all the comments from the all worksheets in a workbook, follow below given steps:-

Open VBA Page press the key Alt+F11.

Insert a module.

Write the below mentioned code:

Sub DeleteAllComments()
Dim wsh As Worksheet
Dim CmtAs CommentFor Each wsh In ActiveWorkbook.Worksheets
For Each Cmt In wsh.Comments
Cmt.Delete
Next
Next
End Sub

Code Explanation: – First, we need to select the subject name, and then we will run the loop to check the sheets, and then delete all the comments from all the sheets.

To run the macro, press the F5 key on your keyboard. All comments will get deleted from the all the sheets in a workbook.

How to hide comments partially?

Let’s take an example to understand how we can hide the comments partially.

We have data in two sheets in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet. In Excel, we have some comments that we want to hide.

To hide the comments from the all the worksheets, follow below given steps:-

Code Explanation: – First we will gave the subject name, then we have define the code to hide the comments.
To run the macro press the F5 key on your keyboard. All comments will get deleted from the all sheets to a workbook.

How to hide comments completely?

Let’s take an example to understand how we can hide the comments completely.

We have data in two sheets in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet. In Excel we have some comments that we want to hide completely. It means comments should be there but it should not appear to anyone but user will be able to edit the comment. As result, this is not a safe method to completely protect the comments from user.

Code Explanation: – First we need to select the subject name, and then we have to define the code to hide the comments completely.
To run the macro, press the F5 key on your keyboard. All comments will be hidden completely from the Excel worksheet.

How to show single comment and it should be always visible?

Macro is used to display certain important comments throughout a worksheet or workbook in Excel.

Let’s take an example to understand how we can show the single comment in Excel.

We have data in two sheets in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet. In Excel, we have some comments that we want to show in the single comment out of N numbers comment.

Code Explanation: – First we decide the subject name, and then we have to define the code to show the single comment in the worksheet.
To run the macro, press the F5 key on your keyboard. Only one comment will appear and other comments will remain hidden.

How to show all the comments within an entire Excel workbook?

This is helpful when we get any workbook from someone and we don’t know which cell is having comments in the workbook, so we can use this VBA code to show all the comments within a workbook.

Let’s take an example to understand how we can show all the comments within entire Excel workbook.

We have data in two sheets in which we have In and Out time of every employee and also, we have recorded working hours, overtime hours and regular hours in the sheet with few comments. But all the comments are hidden and we want to see all the hidden comments.

Code Explanation: – First we need to decide the subject name, and then we have to define the code to show all the comments in the worksheet.
To run the macro, press the F5 key on your keyboard. All comments will appear in the Excel sheet.

Hide Specific Comments in Excel- Comments will still display.

How to hide specific comment in Excel?

This is helpful to hide the few comments which we do not want to show everyone in a data.

Let’s take an example to understand how we can hide some specific comments within an Entire Excel workbook.

We have data in two sheets in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet.

Code Explanation: – First we need to decide the subject name, and then define the ranges which we want to invisible.
To run the macro, press the F5 key on your keyboard. Only 2 comments will appear out of 4.

How to Add Background Photos/Images Comment in Excel?

How to add background of photos or images in comment box?

This macro will be used to set photos or images in the background to the comment box from the computer. It will make comments and Excel look more attractive.

Let’s take an example to understand how we can add background of photos or images in comment box.

We have data in two sheets in which we have In and Out time of every employee and also we have recorded working hours, overtime hours and regular hours in the sheet with few comments in which we want to add pictures or images.

To add the pictures or images in comment box, follow below given steps:-

Code Explanation: – First, we need to select the subject name, and then define the range where we want to add comment, and from where we want to add picture in the comment box.
To run the macro, press the F5 key on your keyboard, and comments will appear with the images in the comment box.

This is the way we can create the comments, hide, delete, insert the image in comment box through VBA in Microsoft Excel.

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

disable

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.