How to Populate Comment from Another Cell in Excel (and Vice Versa)

Get FREE Advanced Excel Exercises with Solutions!

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 comments from another cell in Excel with VBA and then some more.


Populate Comment from Another Cell in Excel: with Easy Steps

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. However, 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 lives 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.

Steps:

  • First of all, go to the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group.

excel populate comment from another cell

  • 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 F5 to run the code. This will automatically populate comment all the cells in the range from the cells beside it.

excel populate comment from another cell

Read More: How to Extract Comments in Excel


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 comments 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 comments from the active cell.

Steps:

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

excel populate comment from another cell

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

excel populate comment from another cell


How to Populate Cell from Comment in Excel

In a similar fashion, we can use VBA to populate a cell from a comment in Excel. Let’s consider a scenario like this.

excel populate comment from another cell

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 a comment in Excel.

Steps:

  • First of all, select the range of cells you want to populate.

  • Then select the Developer tab on your ribbon.
  • After that, click on Visual Basic from the Code group.

excel populate comment from another cell

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

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.


Conclusion

That concludes our discussion on how to populate comments from another Excel cell. Hope you are able to populate comments from another cell, or active cell, and populate a 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.


Related Articles


<< Go Back to How to Add a Comment in Excel | Comments in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo