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

To reproduce:

1. Open the workbook in Excel, and click on the Sheet1 tab.
2. Holding down the Shift key, click on Sheet2. This causes sheets 1 and 2 to be grouped.
3. Right-click any cell. The "Insert Comment" option is gone!
4. Under Review/Comments, the "New Comment" option is grayed!

To fix:

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.

more