Tuesday, November 3, 2015

Excel hyperlink screen tip as comment

Tags:
Excel cell comment tricks
Excel cell hyperlink to make comment
Excel cell hyperlink screen tip to comment
Excel cell comment without arrow
Excel cell comment cell without top-right red corner triangle

The trick is done by creating a fake/dummy/useless hyperlink that refers to nothing (this can be done only programmatically), or you can create a hyperlink that refers to the cell itself using the "insert hyperlink" dialog.

To insert a hyperlink for a given cell, right click on the cell and press " Hyperlink...".




"Insert hyperlink" dialog will popup; Press "Place in this document" button on the left side, select the sheet, edit range, and finally press "ScreenTip..." button and write your text (comment).







After applying whatever the comment you want, it will look like the following capture below.



Programmatically, a single line of code is needed to do this :)




Disadvantages:

• Maximum number of characters per hyperlink screen tip is 256 characters

• You can't add new line using dialog. This only can be done programmatically using VBA code as shown before.

• You can't customize any thing in the screen tip: dimensions (width, height), font type, font size, font color, fill color, font effects (Bold, Italic, Underline),..., etc

• Screen tip has maximum width and maximum height limits. The example below show a multi-line screentip which is done using VBA code.




This is the code to generate the last screen tip.




Advantages:

• Extra parameter for saving data for cell

• No connecting arrow from comment to cell (personally, I don't like the shape of the arrow)

• The cell will not have top-right red corner triangle which in sheet full of comments with small cells will look horrible

• If cell is located at the most-left of your screen, screen tip will be shown in the visible region while the comment will not.

• Once the cell is deleted, hyperlink is removed, and the screen tip also is removed. In comments, you have to delete the comment manually.

• You can assign a screen tip to number cells at once by inserting the hyperlink for this range of cells.

It is done...


1 comment:

  1. How do you proceed to second line when using code to add multi-line in the screen tip dialogue box? Hitting return will exit out of hyperlink screen tip dialog box.

    ReplyDelete