Yes. In desktop Excel, VBA can automatically add a note to a cell with Range.AddComment; in current Microsoft 365 versions, VBA can also add a threaded comment with Range.AddCommentThreaded. The example below adds notes from a neighboring column, and a worksheet event example shows how to add one when someone enters a value.
Comments and notes are different in modern Excel
Excel’s terminology can be confusing because the feature once called a comment was changed into a threaded conversation. In current Excel, a threaded comment supports replies and is intended for collaboration. The older, single-text annotations are now called notes. Existing workbooks may still contain notes created before the terminology changed.
The distinction matters in VBA. The established Range.AddComment method creates a note, not a threaded conversation. A note is a good fit for a macro that adds a fixed explanation, an instruction, or a value copied from another cell. If your requirement is specifically a conversation with replies, use Range.AddCommentThreaded in an Excel build that supports it, and check the workbook’s collaboration behavior before distributing the macro.
This article’s main examples use notes because they are straightforward to create, inspect, and update through the traditional VBA object model. In the Excel interface, notes are available from the Review tab; threaded comments are also managed there. Do not assume that a VBA macro using AddComment will create the modern threaded type just because the interface labels the feature “Comments.”
Add notes from a neighboring column
This macro reads explanatory text from column B and adds it as a note to the cell in column A on the same row. It starts at row 2, so row 1 can hold headings. It skips empty source cells and leaves existing notes unchanged. Change the sheet name and row limits to match your workbook.
Sub AddNotesFromColumnB()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim targetCell As Range
Dim noteText As String
' Change the sheet name to match your workbook.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For r = 2 To lastRow
Set targetCell = ws.Cells(r, "A")
noteText = Trim$(CStr(ws.Cells(r, "B").Value2))
If Len(noteText) > 0 Then
' Do not replace a note that is already present.
If targetCell.Comment Is Nothing Then
targetCell.AddComment noteText
End If
End If
Next r
End Sub
For example, if B2 contains “Confirm the supplier code” and A2 contains a product ID, running the macro adds that sentence as a note to A2. The same logic applies down the rows. The last row is found from column B, so a populated source cell in B is considered even if the corresponding A cell is blank; Excel can attach a note to a blank cell too.
The test for an existing note is intentional. If you want the macro to replace old notes instead, decide that explicitly rather than silently overwriting user content. A replacement version can delete the old note before calling AddComment, but deletion is a destructive change and should be tested on a copy.
Put the macro in a standard module and run it
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste the first macro into that module. Close or switch back to Excel, then choose Developer > Macros, select AddNotesFromColumnB, and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot store VBA code. On a Mac, VBA is available in desktop Excel; the editor shortcut can vary by keyboard, so use Developer > Visual Basic if the shortcut does not work. Excel for the web can open a macro-enabled workbook but cannot run or edit its VBA macros.
Before running any macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, including adding, changing, or deleting notes. Close the workbook without saving if you need to discard a test run and have not made other changes you want to keep.
Add a note automatically when a cell changes
A standard macro runs when you launch it. To respond whenever a user edits a cell, use the worksheet’s Worksheet_Change event instead. This example watches A2:A1000 and adds a note containing the entered value, but only if the cell does not already have a note. Paste this code into the code module for the specific worksheet, not into a standard module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedCells As Range
Dim cell As Range
Dim eventsWereEnabled As Boolean
Set changedCells = Intersect(Target, Me.Range("A2:A1000"))
If changedCells Is Nothing Then Exit Sub
eventsWereEnabled = Application.EnableEvents
On Error GoTo CleanUp
Application.EnableEvents = False
For Each cell In changedCells.Cells
If Len(CStr(cell.Value2)) > 0 Then
If cell.Comment Is Nothing Then
' Store the entered value as a note.
cell.AddComment "Entered value: " & CStr(cell.Value2)
End If
End If
Next cell
CleanUp:
Application.EnableEvents = eventsWereEnabled
End Sub
To install it, press Alt+F11, find the workbook in Project Explorer, and double-click the worksheet that should be monitored. Paste the event procedure into that worksheet’s code window. The Intersect check limits the event to the specified range, and handling each cell means pasting several values at once is supported. If a user edits a cell that already has a note, this version leaves it alone rather than replacing it.
Worksheet events run only when macros are enabled and the workbook is open in desktop Excel. They do not run in Excel for the web. The event procedure also does not run merely because a formula’s calculated result changes: Worksheet_Change responds to edits or pasted values, not recalculation. For formula results, a different event strategy may be needed, and it should be designed carefully to avoid repeated updates.
Choose the right note behavior
Before automating annotations, decide what should happen when the macro encounters an existing note, a blank source, or a multi-cell paste. These choices affect whether the process is safe to repeat and whether it preserves information entered by people.
- Skip existing notes: safest when users may have added their own explanations. The sample macro uses this behavior.
- Replace existing notes: useful when the source column is the authoritative text, but it removes previous note content. Test the replacement logic on a copy first.
- Remove notes when the source is blank: only do this if a blank source definitively means the annotation should be deleted; otherwise, a temporarily empty cell could erase useful context.
- Limit the target range: use a specific range such as A2:A1000 when you do not want headings or unrelated cells affected.
- Define the note text: decide whether it comes from another cell, a fixed message, a timestamp, or a combination. Avoid including sensitive information that should not be visible to workbook users.
For notes created with VBA, the text can also include a line break using vbCrLf. For example, a note can put a status on one line and an instruction on the next. Long notes are less convenient to read, so a short summary in the note with detailed information stored in a table is often easier to maintain.
Common errors and how to fix them
- “Subscript out of range”: the worksheet name in
Worksheets("Sheet1")does not exactly match a tab name, or the sheet is not inThisWorkbook. Check spelling and punctuation. - The macro appears to do nothing: confirm that the source column contains text below row 1, the macro is in a standard module, and macros are enabled. The sample deliberately skips blank source cells and existing notes.
- The note is on the wrong cell: check the target and source column references. In the example, column B supplies the text and column A receives the note.
- Run-time error on a protected sheet: protection can prevent changes to cells or notes. Unprotect the sheet using the workbook’s authorized process, or revise the protection setup; do not embed a password in a distributed macro without considering who can access it.
- Event code stops responding: check that it is in the correct worksheet module and that events have not been left disabled by other VBA code. The sample restores the previous
Application.EnableEventsstate when it exits.
Other edge cases include merged cells, filtered lists, and very large pasted ranges. Test the macro against a small representative set first. If data is arranged as an Excel Table, use its actual columns and headers deliberately rather than assuming that adding rows will automatically change a hard-coded event range such as A2:A1000.
Notes, threaded comments, and alternatives
Use a note when the information is a static annotation attached to a cell, such as a definition or a data-entry reminder. Use a threaded comment when colleagues need to discuss an item and reply to one another. In supported Microsoft 365 desktop builds, the VBA method Range.AddCommentThreaded is intended for creating threaded comments; the note example above does not call it. Availability and collaboration behavior can vary with Excel version and account setup, so confirm the method in the VBA Object Browser for the installation where the macro will run.
If the real goal is to display a message only when someone selects a cell, Data Validation input messages may be a better fit than notes. They are configured through Data > Data Validation and do not create a conversation or a persistent annotation. If information needs to be searchable, sortable, or used in reports, put it in a dedicated worksheet column instead. Notes are visually attached to cells, but they are not a substitute for structured data.
Frequently Asked Questions
Can VBA add a comment to every used cell?
Yes, but define “used” carefully. A macro can loop over a chosen range or detect the last row and column, then add notes only where a condition is met. Start with a bounded range and test it; looping over every cell in a whole worksheet is needlessly broad for most tasks.
Does Range.AddComment create a threaded comment?
No. Range.AddComment creates a legacy-style note in current Excel terminology. For a threaded conversation, use Range.AddCommentThreaded where that method is supported.
Can I automatically add a comment when a value is entered?
Yes. Put a Worksheet_Change event procedure in the relevant worksheet’s code module, restrict it with Intersect, and decide how to handle existing notes and multi-cell pastes. The event example above adds a note for edits in A2:A1000.
Can Excel for the web run this VBA macro?
No. Excel for the web can work with many workbook features, but it does not run VBA macros. Open the macro-enabled workbook in desktop Excel to run or edit the VBA code.
Can I undo notes added by a macro with Ctrl+Z?
No. Excel’s normal Undo history does not undo VBA actions. Save a backup copy before running the macro, or write and test a separate cleanup macro if you need a controlled way to remove notes later.
Related
- How to Create Goal Seek in Excel: A Practical Guide
- Excel Hangman Solver: Build a Word-Guessing Tool
- Goal Seek in Excel: A Practical Sample
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Outlook: How to Create a Contact GroupIn classic Outlook for Windows, VBA can create a contact group by making an Outlook DistListItem, adding resolved…
- VBA Subscript Error Handling: Causes, Checks, and FixesIn VBA, a subscript error usually means Error 9, “Subscript out of range”: code asked for an array…
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- Excel VBA: How to Use Not IsErrorIn Excel VBA, write If Not IsError(value) Then to test whether a value is not an Excel error…
- Excel VBA: Find the Last Cell with Data in a ColumnIn Excel VBA, use Cells(Rows.Count, columnNumber).End(xlUp).Row to find the last nonblank cell’s row in a column. Check for…
- VBA Send Email to Multiple RecipientsYes. In Excel VBA on Windows, you can create an Outlook email and add several addresses to its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.