Email:      Pass:    Pass?

Add Comments to Cells with an Excel Macro

Add comments to cells in Excel with this macro. This allows you to quickly and easily add a comment to any cell within a worksheet or workbook in Excel.

In order to use this macro simply replace the A1 cell reference with the range object with the cell that you want to add a comment to and also replace the text This is my comment. with the text that you would like included in the comment. Make sure to put your comment within the quotation marks in the macro.
Where to install the macro:  Module

Excel Macro to Add Comments to Cells

Sub Add_Comment()
'Adds a comment to cell A1 in Excel

Range("A1").AddComment ("This is my comment.")

End Sub




How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.



Similar Helpful Excel Resources
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell.
This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell.
Why does my macro treat an empty cell as if it has a comment?
Code:

Sub AddComment()
y = GetSetting("LastComment", "Variables", "x")    'Get last value form registry
x = InputBox("Enter text", "Enter Comment", y)    'Enter a value or use default
SaveSetting "LastComment", "Variables", "x", y    'Save last value

Set sCom = ActiveCell.Comment
If Not sCom Is Nothing Then 'check for existing comment
    ActiveCell.Comment.Delete 'delete existing comment
    ActiveCell.AddComment.Text Text:="35:" & Chr(10) & _
            "Do not delete comment"    'Add comment to cell
    ActiveCell.Value = x    'Add value to cell
Else
    MsgBox "Cell already contains a comment"
End If
End Sub


Any hints, tips or examples are appreciated.

All,

I am looking for a code that will take comments from multiple cells within a row and return the product into one cell in a difrrent worksheet. I have tried everything with no luck. Can someone please help me out?

Thanks

Hi all,

Reference the archives at:
http://www.mrexcel.com/archive/VBA/17448.html

How can I modify this sub to skip cells that are empty? I only want cells that are populated within the range to actually display the comment indicator, with the cell text transferred to the comment.

Ideally, I'd also like the text to be moved (not just copied) to the comment and leave an 'X' in the cell, in place of the text that was transferred to the comment, but I'll settle for the action above.

Thanks for any and all suggestions!

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

Any help would be greatly appreciated.

Thanks.

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

Any help would be greatly appreciated.

Thanks.



Hi all,
Im new to this so please bare with me :P !! i have only started using macros recently so not good at it at all. here is the story i have around 30 something worksheets all with the same format and i am interested in 2 columns from each worksheet. one column contains numbers of observations for different headings and the other column contains a description of each observation.

what i am trying to do is copy the number column from each of my 30ish worksheets and plot them into one summary sheet beside each other which i have managed to do as shown in the code below, the second thing i want to do is insert my observation column as a comment for each of the corresponding cells in the summary sheet. i have being trying to do it but no luck . if any one can lead me in the right path or maybe come up with a better alternative that would be very helpful thanks.

the code i have so far:
Sub HoneyBoo()

' introduces variales
Dim Sht As Worksheet
Dim ShtName As String

' Deletes previous content in summary page
Sheets("Summary").Select
Cells.Select
Selection.Delete Shift:=xlUp

' pastes in the headings
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("B1 Canteen").Select
Range("A8:G33").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Summary").Select
Range("A1").Select
ActiveSheet.Paste

' loop for pasting the values in
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Summary" Then
Sht.Select
ShtName = ActiveSheet.Name
Range("H9:H33").Select
Selection.Copy
Sheets("Summary").Select
Range("IV1").End(xlToLeft).Offset(0, 1).Select
ActiveCell.Value = ShtName
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Else
End If
Next Sht


' inserting a blank rows & coloumn

Sheets("Summary").Select
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").ColumnWidth = 3.5
Range("I32").Select

'making the headings Bold
Range("I2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True

' autofitin the coloumns
Columns("I:CW").Select
Columns("I:CW").EntireColumn.AutoFit

Range("A1").Select
End Sub
Hi All,

I have this simple macro that enters a formula in a given range. Works great, but after it's all said and done, the cells contain the green comment like thing in the upper left corners. It basically says that "Formulas Omit Adjacent Cells." I know what it means but is there a way to eliminate all these comments in the same range within the same macro? I tried using the recorder but it didn't work.

The code:

Range("N21").Select 'PERCENTAGE COMPLETE Column
ActiveCell.Value = "=IF(ISERROR(L21/H21),0,(L21/H21))"
Range("N22").Select
PC = 22
For Counter = 1 To 200
PerCom = "=IF(ISERROR(L" & PC & "/H" & PC & "),0,(L" & PC & " /H" & PC & "))"
ActiveCell.Value = PerCom
ActiveCell.Offset(1).Select
PC = PC + 1
Next Counter

Thanks.

we want to be able to print the comments and or hyperlinks, connected to a
particular cell and the data validation information.



I have two sheets in a workbook.

Sheet 1 contains a table of shipments broken down by product, shipment date, and number of shipments.
Sheet 2 contains comments/notes on any issues with a particular shipment.

I am trying to add each comment in the issues table to the corresponding cell in the shipments table. Is there a way to do this? Example tables attached. Thanks.

Can you help me by editing this code so that every instance of the username
is in bold whether it is a new comment or an add to comment? I would prefer
just the username be in bold but the username,hour,time line being all bold
as it is now is acceptable. I tried just about everything I can think of so
far so HELP!

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String
Dim lName As Long

strDate = "ddmmmyy hh:mm"
Username = Application.Username
Set cmt = ActiveCell.Comment
lName = 0

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Username & " " & Format(Now, strDate) & Chr(10)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Username & Format(Now, strDate)
lName = InStr(1, cmt.Text, Chr(10)) - 1
cmt.Shape.TextFrame.Characters.Font.Bold = False
cmt.Shape.TextFrame.Characters(1, lName).Font.Bold = True
End If

End Sub


Subscribe for Free Excel tips & more!
E-mail:
Submit Inquiry Here
  • Prices From $10
Name:*
E-mail:*
Request:*
The macro(s) on this page will be sent with the request.