Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: OWC11 spreadsheet addcomment to cell

    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.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

  3. #3
    Join Date
    Jun 2010
    Posts
    3
    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.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

  5. #5
    Join Date
    Jun 2010
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •