I'm trying to use the following to add comments to a cell in a Microsoft Office Spreadsheet 11.0 component on a form in Access 2003

Dim cntlExcel As Control
Set cntlExcel = Me.Controls("myExcelSheet")

cntlExcel.Range("A1").AddComment
cntlExcel.Range("A1").Comment.Visible = False
cntlExcel.Range("A1").Comment.Text = "blah"

which results in "Object doesn't support this property or method" error

I'm doing something wrong, or is it the case that OWC spreadsheet component cannot be used to add comments to cells via VBA code (as per error message).

Thanks in advance.

Hi,

Unlike Excel.Range, OWC11.Range doesn't support comments.

There may be workarounds, depending on when the comments should be shown at what their ultimate purpose is.

Thanks Colin,

I'm using the spreadsheet to produce output from the database in the form of Excel files (and manipulation/display of the output data will be done within Excel). Since the user has a number of options for flagging/handling various scenarios with the data prior to output (eg replace "missing" entries for a date range with either zero, leave as null, or insert "NA" etc) it would be "nice" to tag such manipulated data in the output spreadsheet with a comment. If comments for cells cannot be produced by OWC11, I was thinking of just using cell formatting instead (eg red text indicates tagged/manipulated data).

Thanks again.

Hi,

Colouring sounds like a good solution in this case. Additionally, if you wanted to, you could use the control's MouseOver event handler to update a label on your form with some information as the user moves the mouse over the relevant ranges.

Colin,

You're advice about using Excel event handlers got me thinking.

Using Workbook_Open() to automatically fire-off some code when a user opens the spreadsheet that is output from the database/OWC11 spreadsheet, that searchs through the used range looking for "red text" (that indicates tagged/manipulated data - or different types of formatting which can be used to indicate different options selected during the creation of the spreadsheet by the database) and then converting this to comments, is a bit of cludge but gives the desired result.

Regards