Comment Range Properties

The following properties are available on CommentRange objects, which appear as expandable sub-properties of the objects that use them.

Attachments

Property Type Default Description

AllowAttachments

string

False

Controls whether to allow files to be attached to a cell.

AllowAttachments can be specified as a formula that evaluates to True or False.

If AllowAttachments is blank a value of False will be used.

AllowDeleteAttachments

string

True

Controls whether to allow attachment to be deleted.

AllowDeleteAttachments can be specified as a formula that evaluates to True or False.

If AllowDeleteAttachments is blank a value of True will be used.

AllowedAttachmentFileTypes

string

A semicolon delimited string of file types. For example, ".xlsx;.txt;.docx".

Only attachments of the file types specified are allowed to be attached.

If no file types are specified, then all types are allowed, except for restricted types, which include files with any of the following extensions: exe, pif, application, gadget, msi, msp, com, scr, hta, cpl, msc, jar, bat, cmd, vb, vbs, vbe, js, jse, ws, wsf, wsc, wsh, ps1, ps1xml, ps2, ps2xml, psc1, psc2, msh, msh1, msh2, mshxml, msh1xml, msh2xml, scf, inf, reg, docm, dotm, xlsm, xltm, xlam, pptm, potm, ppam, ppsm, sldmS1

AttachmentCountFillColor
displayed as CountFillColor

Color

LightGoldenrodYellow

Specifies the fill color of the attachment count.

AttachmentCountFontColor
displayed as CountFontColor

Color

Black

The font color of the attachment count.

AttachmentCountFontSize
displayed as CountFontSize

double

6

The font size of the attachment count.

AttachmentCountLineColor
displayed as CountLineColor

Color

Black

Specifies the color of the border of the attachment count.

AttachmentCountLineTransparency
displayed as CountLineTransparency

double

0.0

Specifies the transparency level of the attachment count’s border.

Valid values are 0.0 (opaque) to 1.0 (transparent).

AttachmentCountLineWeight
displayed as CountLineWeight

double

0.5

Specifies the weight in points of the attachment count’s border.

AttachmentCountShow
displayed as CountShow

bool

false

Whether to show the attachment count rectangle in upper left of a comment cell.

AttachmentCountSizeHeight
displayed as CountHeight

int

6

The height in points of the attachment count box.

AttachmentCountSizeWidth
displayed as CountWidth

int

6

The width in points of the attachment count box.

AttachmentCountTransparency
displayed as CountTransparency

double

0.0

Specifies the transparency level of the attachment count.

Valid values are 0.0 (opaque) to 1.0 (transparent).

MaximumAttachmentFileSize
displayed as MaximumFileSize

int

1024

The maximum allowed file size of an attachment in KB.

MaximumNumberOfAttachments
displayed as MaximumAttachments

string

1

The maximum number of files that can be attached to a cell.

MaximumNumberOfAttachments can be specified as an integer value or a formula that evaluates to 0 or greater.

If MaximumNumberOfAttachments is blank a value of 1 will be used.

Comments

Property Type Default Description

AllowAddString
displayed as AllowAdd

string

True

Controls whether to allow comments to be added to a given cell within the comment range.

AllowAdd can be specified as a formula that evaluates to True or False.

AllowComments

bool

true

Controls whether to allow comments.

A comment range can allow comments and/or allow attachments.

AllowDeleteString
displayed as AllowDelete

string

False

Controls whether to allow comments that have already been saved to be deleted.

AllowDelete can be specified as a formula that evaluates to True or False.

The following tokens are available for the current comment: [T.Comment.CreatedBy], [T.Comment.CreatedDate], [T.Comment.UpdatedBy], [T.Comment.UpdatedDate].

AllowUpdateString
displayed as AllowUpdate

string

False

Controls whether to allow comments that have already been saved to be updated.

AllowUpdate can be specified as a formula that evaluates to True or False.

The following tokens are available for the current comment: [T.Comment.CreatedBy], [T.Comment.CreatedDate], [T.Comment.UpdatedBy], [T.Comment.UpdatedDate].

AutoUpdateSameCommentInOtherCommentRanges

bool

false

Controls whether the other comment ranges should automatically be searched for the same comment when a comment in this comment range is added/changed/deleted. If found, the comment value within the other comment range on the sheet will be updated to reflect the change to the comment in this comment range.

Since there can be a signficant performance impact when this setting is True, the setting should only be set to True when comments in this comment range also exist in one or more of the other comment ranges based on an exact match of the resolved key item string.

ClearCellsOnRefresh

bool

true

Determines whether existing cell values are retained when comments are written to the worksheet.

CommentIndicatorPolicy

CommentIndicatorPolicy

CommentIndicatorPolicy.ExcelComment

Controls what to use for a comment indicator on cells that have comments.

ExcelComment - An Excel comment is added to a cell, which has associated comments, and displays the text of all the comments. The ExcelFormat setting controls the presentation of the comments.

CommentIndicator - This option has been deprecated.

None - No comment indicator is presented.

CommentsExplorerShowLoadButton

bool

true

Whether to show the LoadComments button in the comments explorer when the active cell is in a comment range but the comments have not been loaded yet.

CommitPolicy

CommentCommitPolicy

CommentCommitPolicy.OnCellInput

Specifies the commit behavior for the comments in the comment range.

  • OnCellInput: Automatically commit any changes entered into a comment cell.

  • User: Allow user to determine when to save comments. Depends on the CommentSave tool in the view’s toolbar configuration.

EditPolicy

CommentEditPolicy

CommentEditPolicy.EditInCommentsExplorer

Specifies how comments may be edited.

  • EditInCell: Allow the user to edit the comment in the cell. This policy is not compatible with MostRecentToOldest or OldestToMostRecent CommentInCellDisplayPolicy.

  • EditInCommentsExplorer: Allow the user to edit the coments using the Comments Explorer.

  • EditInBoth: Allow the user to edit the coments using the Comments Explorer or directly in the cell.

EntryWhenNotLoadedMessage

string
Default value

Comments cannot be entered before comments have been loaded.

Load comments now?

The message to prompt with when something is entered into a comment cell and the comment range has not been loaded yet.

If this property is left blank the EntryWhenNotLoadedPolicy will be executed without prompting the user.

EntryWhenNotLoadedPolicy

CommentEntryWhenNotLoadedPolicy

CommentEntryWhenNotLoadedPolicy.PromptForLoad

Specifies what to do when something is entered into a comment cell and the comment range has not been loaded yet.

  • AutoLoad: Load the comments and ignore/undo the input.

  • IgnoreEntry: Ignore/undo the entry.

  • PromptForLoad: Prompt the user for whether they want to load the comment range. If yes the comments in the range will be loaded. Yes or no, the input will be ignored/undone.

ExcelFormat

CommentExcelFormat

CommentExcelFormat.Comment

Specifies how to display the comments in an Excel comment.

  • Comment = <Comment>

  • UserComment = <Username>: <Comment>

  • TimeComment = (<DateFormat>): <Comment>

  • UserTimeComment: <Username> (<DateFormat>): <Comment>

InCellDisplayPolicy

CommentInCellDisplayPolicy

CommentInCellDisplayPolicy.None

Specifies how to display comments within each cell of the comment range.

  • MostRecent: Only the most recent comment is displayed in the cell.

  • MostRecentToOldest: All comments are displayed in the cell (line-feeds after each comment) in order of most recent to oldest. This policy is not compatible with in-cell editing.

  • Oldest: Only the oldest comment is displayed in the cell.

  • OldestToMostRecent: All comments are displayed in the cell (line-feeds after each comment) in order of oldest to most recent. This policy is not compatible with in-cell editing.

  • None: Comments are not displayed in-cell.

LoadPolicy

CommentLoadPolicy

CommentLoadPolicy.LoadAllOnBuild

Specifies when to load the comments of the specified range.

  • LoadAllOnBuild: Load all comments during the view build process.

  • LoadSheetsCommentsWhenSheetFirstViewed: Load comments per sheet, each time a sheet is viewed for the first time.

  • None: Don’t load comments. Assume that it is done via other means.

MaxCommentCharacters

int

-1

The maximum number of characters to display of each comment in the comment cell. Enter -1 for no limit.

SortPolicy

CommentSortPolicy

CommentSortPolicy.DateAscending

Specifies how to sort comments when displayed in Excel Comments or in the Comments Explorer.

  • DateAscending: Sort by date, oldest to most recent.

  • DateDescending: Sort by date, most recent to oldest.

  • None: Don’t sort.

This property is overridden by InCellDisplayPolicy (unless the InCellDisplayPolicy=None).

StoreNumericCommentAsNumberOnSheet

bool

false

Controls whether a numeric comment is stored in the cell as a number or as text. The default setting is False, which results in numeric comments being stored as text. When the setting is True, number formatting can be used to format the values.

ThreadPolicy

CommentThreadPolicy

CommentThreadPolicy.ThreadedMultipleRootComments

Specifes the ThreadPolicy for comments in this range.

  • OneCommentOnly: Allow only one comment per cell.

  • ListWithoutThreads: Allow multiple comments, but no threading.

  • ThreadedOneRootComment: Allow only one comment thread.

  • ThreadedMultipleRootComments: Allow multiple comment threads.

  • Undefined: Use of this policy will prevent any comments or responses from being entered.

General

Property Type Default Description

Caption

string

Identifies each comment range definition in the Comment Range Configurations Editor of the View Metadata Editor and the Comment Range Editor of the View Template Designer.

Unique Captions are not required. However, it is strongly recommened to make them unique, per view, to avoid confusion.

Caption is not used at run time.

DateFormat

string

g

Specifies the format used when displaying a date, such as the created or last modified date, in the Comments Explorer or an Excel comment.

The DateFormat value can either be a standard or a custom format string. The default value, g, combines the short date and short time patterns, as defined by the view’s culture, separated by a space.

A standard format string consists of a single format character specifier from the following list of standard format specifiers. The patterns are influenced by the view’s culture, which defines the short and long patterns as well as the date separator and time separator.

With the following standard format specifiers, the displayed time is the local time. The examples are based on the United States (English) culture:

d: Short date pattern, 4/9/2009 D: Long date pattern, Thursday, April 09, 2009 t: Short time pattern, 7:53 PM T: Long time pattern, 7:53:03 PM f: Combination of long date and short time patterns separated by a space, Thursday, April 09, 2009 7:53 PM F: Full date/time pattern, Thursday, April 09, 2009 7:53:03 PM g: Combination of short date and short time patterns separated by a space, 4/9/2009 7:53 PM G: Combination of short date and long time patterns separated by a space, 4/9/2009 7:53:03 PM

The format specifiers are based on the .NET framework conventions. Refer to the following links for more information about standard and custom format specifiers, all of which are supported for the DateFormat property:

http://msdn.microsoft.com/en-us/library/az4se3k1.aspx http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

SequentialRanges

bool

false

Controls whether to apply the same configuration to all ranges of defined names that start the same Range name and end with a period followed by an integer.

SpellCheckEnabled

bool

false

Linking

Property Type Default Description

Address

string

The address of the range that contains the comments.

The Address is typically a defined name, and must be a defined name when SequentialRanges is True.

BaseKeyItems

ArrayList of NamedKeyValuePair

Key items that are the same for all comments in the range.

BaseKeyItems should typically only be used when the KeyItemsConditionString is not used and the KeyItemsString is not a formula that excludes cells within the comment range.

ContextString

string

The context string is stored with each comment. It can be used to capture context specific information that may be important to understanding the comment.

A formula can be used to specify this string.

HideKeyItemsRanges

bool

true

Whether to automatically hide the key items row and key items column at run-time.

KeyItemsColumnRange

string

A single-column vertical range on the worksheet that holds the key items of each row.

Use KeyItemsColumnRange in conjunction with KeyItemsRowRange to define key items for each intersecting cell in the comment range.

When KeyItemsColumnRange or KeyItemsRowRange are used, the KeyItemsString property is ignored.

KeyItemsConditionString

string

True

A string that evaluates to True or False and controls whether the KeyItemsString is evaluated for any given cell within the comment range.

When used, the KeyItemsConditionString is typically a formula. For example, the following formula excludes blank and locked cells within the comment range: =Not(Or(IsBlank(@ACell()), @CellIsLocked()))

Alternatively, the condition can be included in the KeyItemsString formula using the Excel IF function.

In cases where many of the cells within the comment range are excluded from having comments, performance can sometimes be improved by using the KeyItemsConditionString instead of including the same condition in the KeyItemsString.

KeyItemsRowRange

string

A single-row horizontal range on the worksheet that holds the key items of each column.

Use KeyItemsRowRange in conjunction with KeyItemsColumnRange to define key items for each intersecting cell in the comment range.

When KeyItemsRowRange or KeyItemsColumnRange are used, the KeyItemsString property is ignored.

KeyItemsString

string

A string of key-value pairs, such as \"Product=100; Market=NorthEast; Scenario=Actual\".

A formula can be used to specify this string.