Owing to VBA and formulas, Excel can perform various operations. This includes populating comments from another cell. Of course, you can manually type in or copy the values of those cells into comments. But instead of right-clicking, selecting the option, and then writing, we can simply type in one of the cells to call it again with VBA as input for comment. This method is particularly helpful for a large number of operations. This article will focus on how to auto populate comment from another cell in Excel with VBA and then some more.
Download Practice Workbook
You can download the workbook with the examples used for demonstration and all the VBA codes included in different modules from the link below.
Steps to Populate Comment from Another Cell in Excel
Let’s consider a scenario where we want a series of comments and we are using other cells as a source for the comment’s content. For one thing, we can manually type in every content as a different comment. This method is quite easy and simple- you just have to right-click on a cell, click the New Comment option, and then keep on writing comments. We can keep doing it for the number of comments required. But this method is quite tiresome for a large set of comments and cells. Thankfully, VBA comes to our rescue to automate such a process and make our life a whole lot easier.
For demonstration, let’s consider a small dataset like this.
The dataset contains a series of books along with their author names. We now want a comment on each of the books’ cells and we want the author’s name as its comment material. In other words, we want to have a comment in range C5:C9 with range B5:B9 as the comment.
In any even of VBA code, you need the Developer tab to be in your ribbon first. Enable the Developer Tab on your ribbon if you don’t have it.
Once you have that, follow these steps for the VBA code and how to use it.
- First of all, go to the Developer tab on your ribbon.
- Then select Visual Basic from the Code group.
- As a result, the VBA window will open up. Now select the Insert tab in it and from the drop-down menu, select Module.
- Consequently, this will create a new module in the window. Enter the following code in this module.
Sub show_from_another_cell() Dim cell As Range Set output_range = Range("C5:C9") For Each cell In output_range Text = cell.Offset(0, -1).Value cell.AddComment (Text) Next cell End Sub
You can replace the range with your own range in the code.
- Once you have put in the code, press on F5 to run the code. This will automatically populate comment all the cells in the range from the cells beside it.
- Extract Comments in Excel (3 Suitable Examples)
- How to Reference Comments in Excel (3 Easy Methods)
- [Solved:] Insert Comment Not Working in Excel (2 Simple Solutions)
- How to Export PDF Comments into an Excel Spreadsheet (3 Quick Tricks)
- Print Worksheet with Comments in Excel (5 Easy Ways)
How to Populate Comment from Active Cell in Excel
Now let’s consider the same dataset with the author’s names and book names.
But this time we want to populate comment of cells in the books column with their own values. Luckily, VBA has a solution for that too. Similar to all other VBA procedures, you first need to Enable the Developer tab. Then follow these steps to populate comment from the active cell.
- First of all, select the cells you want to add a comment to.
- Then go to the Developer tab on your ribbon.
- After that, select Visual Basic from the Code group.
- Now click on the Insert tab in the VBA window that opened up. And then select Module from the drop-down list.
- Next, write down the following code in the newly created module.
Sub Show_from_Current_Cell() Dim Cell As Range For Each Cell In Selection Cell.ClearComments If Len(Cell.Value) > 0 Then Cell.AddComment Cell.Comment.Text Cell.Value & "" End If Next Cell End Sub
- Finally, press F5 on your keyboard to run the code.
As a result of these steps, there will be comments on the range C5:C9 populated by its own content.
How to Populate Cell from Comment in Excel
In a similar fashion, we can use VBA to populate a cell from comment in Excel. Let’s consider a scenario like this.
We have comments for each of the books’ column cells here. Each comment represents the book name of the cell. We now want to populate these cells from the comments using Excel VBA. This section will show you how to do that.
Like any other VBA code, you need the Developer tab to perform this operation. Enable the Developer Tab on your ribbon if you don’t have it. After that, follow these steps to populate the cell from comment in Excel.
- First of all, select the range of cells you want to populate.
- Then go select the Developer tab on your ribbon.
- After that, click on Visual Basic from the Code group.
- As a result, the VBA window will now pop up. In this new window, select Insert and then select Module from the drop-down options.
- Thus a new module will be created. Now open the module and write down the following code in it.
Sub Conmment_To_Cell() Dim Cell As Range For Each Cell In Selection Cell.Value = Cell.NoteText Next Cell End Sub
- Finally, press F5 on your keyboard to run the code.
As a result, the cells will be populated by the existing comment of each cell.
Things to Remember
- Replace the range in the first code with your desired cell range, if you want to use it for a different dataset.
- The first code only takes the value of the cell from the left to populate the comments.
- Select the cells before running the second and third VBA codes.
- For a workbook with multiple modules of code, check the correct code sub before running it as the VBA results are irreversible in Excel.
- Always backup your file before attempting such procedures.
That concludes our discussion on how to populate comment from another Excel cell. Hope you are able to populate comments from another cell, active cell, and populate cell from a comment now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like these, visit Exceldemy.com.
- How to Extract Comments from Word Document into Excel
- Hide All Comments in Excel (3 Simple Methods)
- Convert Comments to Notes in Excel (3 Suitable Ways)
- How to Read Full Comment in Excel (6 Handy Methods)
- Print Comments in Excel (4 Suitable Ways)
- How to Find Comments in Excel (4 Easy Methods)
- Remove Pop-up Comments in Excel (4 Suitable Ways)