How to use comment option through VBA

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.

 

image 1

 

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

  • Open VBA Page press the key Alt+F11.
  • Insert a module.

Write the below mentioned code:

Sub AddComment()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
sh.Range("E10").AddComment ("Saturday off")
sh.Range("D12").AddComment ("Total Working Hours - Regular Hours")
sh.Range("I12").AddComment ("8 Hours Per Day Multiply by 5 Working Days")
sh.Range("M12").AddComment ("Total working hours 21-July-2014 to 26-July-2014")
End Sub

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.

image 2

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.

 

image 3

 

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

image 4

 

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.

 

image 5

 

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.

 

image 6

 

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.

 

image 7

 

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

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
  • Write the below mentioned code:

 

Sub HideComments()
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

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.

 

image 8

 

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.

image 9

To hide the comments completely, follow below given steps:-

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
  • Write the below mentioned code:

 

Sub HideCommentscompletely()
Application.DisplayCommentIndicator = xlNoIndicator
End Sub

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.

image 10

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.

image 11

To show the single comment, follow below given steps:-

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
    • Write the below mentioned code:

 

Sub AddComment()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
sh.Range("E10").Comment.Visible = True
End Sub

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.

image 12

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.

image 13

To show the single comment, follow below given steps:-

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
  • Write the below mentioned code:

 

Sub ShowAllComments()
Application.DisplayCommentIndicator = xlCommentAndIndicator
End Sub

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.

image 14

  • 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.

image 15

To hide the some specific comments, follow below given steps:-

 

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
  • Write the below mentioned code:

 

Sub HideSpecificComments()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
sh.Range("E10").Comment.Visible = False
sh.Range("D12").Comment.Visible = False
End Sub

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.

image 16

  • 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.

image 17

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

  • Open VBA Page and press the key Alt+F11.
  • Insert a module.
  • Write the below mentioned code:

 

Sub AddPictureComment()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
sh.Range("E10").AddComment ("Saturday off")
sh.Range("E10").Comment.Shape.Fill.UserPicture "D:\Data\Flower.jpg"
sh.Range("D12").AddComment ("Total Working Hours - Regular Hours")
sh.Range("D12").Comment.Shape.Fill.UserPicture "D:\Data\Flower.jpg"
End Sub

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.

image 18

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

image 19 image 20

 

Users are saying about us...

  1. Pingback: cleantalkorg2.ru

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube