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.

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.

How do I 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:-

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.

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.

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.

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.

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.

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.

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.

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.

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.

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:-

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.

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

Related Articles:

Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.

VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.

Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.

ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.

Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.

Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc

Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.

Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

qspefwrue20
October 28, 2020 at 3:02 pm

?? ????????
_________________
?????? ?????? ???????????

Reply ↓
khgevjtrd20
October 27, 2020 at 6:55 pm

???????? ?????????
_________________
?????????? ?????? ???????????

Reply ↓