|
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
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- 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.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 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.
- 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,
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
I know how to print all comments at the end but I need a macro that will print the comments with the value of the cell, not the cell number itself. I have a lot of comments; usually phone numbers for people. When I print out the comments, it gives the cell number. I have to look up the cell number and see the person's name in that cell to associate it with the phone number (example below).
A nice macro that would print the persons name and the comment (if any) would be great. Been searching on net but no luck so far.
Example.
I am getting:
Cell D125
212-512-1234
Cell D125 contains Joe Jones. I would like to get:
Joe Jones (or Cell D125 Joe Jones)
212-512-1234
Thanks.
|
|
