I am attempting to write a subroutine within a MS Access module that accesses a MS Excel worksheet and adds a comment to a specified range. I am using late binding so that my low-confidence users don't need to fiddle around adding references. When attempting to add comments to a range object, the following error is thrown:
Run-time error '1004': Application-defined or object-defined error.
With the following example code, assume that the worksheet opened has a Cell A1 with the comment "Hello", Cell A2 with the comment "World", and Cell A3 does not have a comment.
Sub export(strFilePath As String) Dim XL As Object Dim WB as Object Dim WS as Object Set XL = CreateObject("Excel.Application") XL.Workbooks.Open strFilePath Set WB = XL.Workbooks(1) Set WS = WB.Sheets("ExportSheet") Debug.Print WS.Comments.Count 'returns accurate number of comments on sheet (2) Debug.Print WS.Range("a1").Comment.Text 'returns comment of cell A1 (Hello) WS.Range("a1").Comment.Delete 'removes comment of cell A1 Debug.Print WS.Comments.Count 'returns accurate number of comments on sheet (1) Debug.Print WS.Range("a2").Comment.Text 'returns comment of cell A2 (World) WS.Range("a2").ClearComments 'removes comment of cell A2 Debug.Print WS.Comments.Count 'returns accurate number of comments on sheet (0) WS.Range("a3").AddComment("HelloAgain") 'causes 1004 error WS.Range("a3").AddComment "HelloAgain" 'also causes 1004 error, if the above line is commented out End Sub
I am able to access the methods pertaining to comments except for the
range.AddComment method. How do I go about adding a comment?
This error occurs when cell A3 already contains a comment. I was able to reproduce it.
>>>EDIT - problem occurs when trying to add a comment to any cell
The problem also occurs if the worksheet is grouped with another worksheet. See http://www.pcreview.co.uk/forums/did-lose-ability-add-edit-comments-excel-t3837597.html
- Open the workbook in Excel, and click on the Sheet1 tab.
- Holding down the Shift key, click on Sheet2. This causes sheets 1 and 2 to be grouped.
- Right-click any cell. The "Insert Comment" option is gone!
- Under Review/Comments, the "New Comment" option is grayed!
If this is indeed your problem, then try adding a line of code to ungroup the worksheets.
Set WS = WB.Sheets("ExportSheet") WS.Select ' ungroup worksheets <-- ADD THIS CODE
I discovered how to ungroup the sheets using VBA by recording a macro. I right-clicked on the Sheet tab and chose "Ungroup Sheets", then looked at the generated code.