XLplus Add-On: Rework Comments

Using Change Comments allows changing the comment of the active cell, all comments of the current selection of cells or all comments of the active sheet. You can change a comment by:

• Adding, Removing or changing the Author (or any other text) of a comment
• Adding the actual Date to a comment
• specifying the Display status of Comments (Shown or Hidden).

Author:

Pic. 2: Author

 

By default, comments within a MS Excel worksheet display the Author of the comment within the text of the comment. Using the Author options allows to

• Add
• Replace
• Delete

the specified Author.

Note: This does not change the Author Property of a comment but only the display of an Author within the comments text since the Author Property property is ReadOnly and will be assigned by MS Excel autonomically (S. Details).

Tip: Even if the main purpose was replacing or adding Authors you can use this option for replacing or adding any text.

Details:

When replacing or deleting All Authors the Wizard will identify all comments to work with by the Author property of a comment. There might exist comments which show a different Author Name than defined within the property, i.e. because it was replaced previously. In this case no replacement or deletion will take place (provided the Author Name of the property does not exist within the comments text at another position).

In order to targetly replace or delete specific Authors use option This Author. This allows changing parts of the comments text as well. For example, replacing the word Comment with Comments will replace the first occurancy of it within a comment.

When replacing or deleting only the first occurancy of a word will be used.

Using Add Author allows adding an Author at beginning of the comments text. Of course you can add any other text, too.

When replacing an Author or any other text, formattings of Tab Format will be applied to the Author (the new text). In order to prevent this switch to Tab Format and deactivate option Format Author Bold. In case the default settings are still in place existing formats will be kept. The new text will be formatted like the replaced text.

You can select Authors from a list. The list will display all Authors whose Author Property could have been read from existing comments. Additionally, the name you use within MS Excel will be shown. The list of Authors will be unique - entries listed twice will be removed. You can change your name, you use in MS Excel, by using (MS Excel 2007) Excel Options - Popular - User name.

When creating a comment MS Excel will set the Author Property to the name you use in MS Excel - regardless of what Author you specified. The Author shown in the comment might be different in case you specified that.

Date:

Pic. 3: Date

 

You can optionaly specify to add a date to your comments. In case Add Date is specified and an existing Author is going to be replaced, the new Author will be shown plus the actual date. You can specify, that dates are shown before or after an Author.

The same will happen in case an Author is added, deleted or a new Comment is created. In all theses cases a comment will be preceeded by a date. In case any other text was replaced the date will be placed before or after the position where the previous text had been placed. In case any text was added it will be added at beginning of the comment. In case any text or an Author was deleted but a date shall be added, the date will take over position of the deleted text.

Display:

Pic. 4: Display

 

Using this, you can show or hide all existing comments of the active worksheet or the current selection with one step. You can combine this setting with changes to an Author or Date.

Using option Format allows to specify how the formatting of your comments shall be treated. By default, all of your formatting will stay intact when changes are applied.

When applying changes to a greater number of comments at a time, keeping formats intact will enlarge the time to process all comments. In order to speed up operations you might want to change the formatting of all comments to process to a simple format.

Use Format entire Comment Normal in order to retrieve comments as plain text. Use Format entire Comment Bold in order to format all comments with bold font settings.

When adding Authors or Dates or changing Authors the new text will be formatted well by default. Deactivate the options Format Author Bold and Format Date Normal and assign font color Grey in order to prevent these formats.

Limitations and Preserved Formats:

Pic. 5: Delete

Use this, in order to delete All Comments, All Comments of the actual Selection, Comment of the Active Cell or all Comments of a specified Author. When selecting option Delete the options Author, Date, Display and Format are not accessible.

Pic. 6: Delete Settings

Pic. 7: Add

Use Add Comment to add new comments to all selected cells. Selecting this option shows a textbox where you can enter your comments text. You do not have to enter a text - this will create a new blank comment.

You can optionaly specify to add an Author to the comment and / or a Date.

A new comment having specified all possible settings might look like Pic. 8.

Pic. 8: New Comment

• Check out Find Comments. Find Comments will return a selection of cells containing all your comments of interest. You can then easily rework this selection of comments.
• Use Shape Comments in order to assign a new shape to all comments of interest.
• Use Highlight Comments to automatically adjust the size of all your comments to their content or to reformat specific text.
• Use Replace Author to change any type of text, i.e. replace MyProductname with MyNewProductname plus Product ID. Make sure you have deactivated option Format - Format Author Bold - only then just the format of the replaced expression will be used. Otherwise the replacing expression will be formatted bold.
• At beginning of your operations check that you have selected the correct cell range. By default only the comment of the Active Cell will be used.
• When reworking comments by keeping the existant formatting: Take the time to chat with colleagues or neighbors - formattings will take a felt eternity
  
  
  
  Replacing the word Comments took ~ 20 seconds on a PC having Windows 7 CPU Score of 6.7 (Intel(R) Core(TM)2 Duo CPU E8500 @ 3.16GHz), Windows 7 and MS Excel 2007.
  
  Note: This might happen much faster on your PC - even by using a slower CPU. Operating System as well as MS Excel Version are more important for processing time than the CPU. Using MS Windows 2000 and MS Excel 2000 will presumably solve this task in 5 seconds - regardless of the CPU used. But this is another story ...

Social Networks