Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Inserting Workbook Comments Into a Cell.
Written by Allen Wyatt (last updated February 18, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016
Jim would like to insert the text found in the Comments portion of a workbook's properties into a cell. This isn't a comment attached to a cell, but the contents of the Comments field in the workbook properties.
If you need to simply copy the comments a single time, then doing so manually may be the best bet. You can display the Comments field, select whatever contents you want to put into your worksheet, and then press Ctrl+C. Close the properties, select the desired cell, and then press Ctrl+V.
If you have more of a need for the inclusion to be dynamic, then the only way to add those comments to a cell is to use a macro. If you want to have the contents appear in a specific cell (such as A1), then you can simply use a single line of code:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Range("A1")=ActiveWorkbook.BuiltinDocumentProperties("comments")
That's it; a single line of code to stuff the comments into the cell. You can build upon this, if desired, to create a user-defined function that is helpful for placing the comments anywhere you desire.
Function putComments() As String
Application.Volatile
putComments=ActiveWorkbook.BuiltinDocumentProperties("comments")
End Function
In order to use this user-defined function, simply use the following in a cell:
=putComments()
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12333) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Inserting Workbook Comments Into a Cell.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you frequently add comments to cells in a worksheet, Excel provides a variety of tools you can use to manage those ...
Discover MoreNeed to find that misplaced comment in your worksheet? It's easy to do using the Find and Replace capabilities of Excel.
Discover MoreAdd a comment to a worksheet, and you'll notice that Excel places a small, red triangle at the upper-right corner of the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-02-19 11:09:51
J. Woolley
You might be interested in the freely available ListDocProperties array function in My Excel Toolbox, which also includes ListAppProperties, ListWBProperties, and many similar array functions. See https://sites.google.com/view/MyExcelToolbox/
2021-02-18 05:44:23
Mike
Tips like this one often get me wondering what other things this method can do.
This macro lists most (if not all) similar properties, using columns A & B in the first sheet.
Sub showprops()
On Error Resume Next
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = ActiveWorkbook.BuiltinDocumentProperties.Item(rw)
rw = rw + 1
Next
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments