For those of you who just came from behind the mountains: a tooltip is a brief explanatory text which appears when you hover your cursor over a button or other control without clicking.
In an Excel spreadsheet, the nearest thing to a tool tip is the Comments functionality.
The good thing about comments is that they have been around a long time, so most people know what they are and how to use them. The bad thing about comments is that they have been around a long time and they just look and feel outdated.
Not to be a curmudgeon here, but with all the fancy fancy that Microsoft has put into Excel, can't they give us a few more options around comments?
Here are a few ideas:
- Comments should activate on key up and down (not just click)
- Ability to hide that red triangle
- Ability to change the triangle to some other shape
- Ability to number Comments
- Ability to put hyperlinks in Comments
- Ability to put formulas in Comments
Creating a ToolTip
If you too are looking for an alternative comments, here is a simple trick to get a tooltip effect in your spreadsheet.
In this screenshot, I've got a simple data entry form and I want to give my users some instructions on how to fill out the form.
I've put in some comments, but there are a couple of things I don't like about them. First, you have to click on the cell in order to make the comment appear. Keying up or down with your keyboard will not activate the comment. Second, everything about it it's damned ugly (the red triangle, the pasty box with the 1997-esque shadow, and the jagged arrow).
Of course my form isn't exactly Web 2.0 either, but that's not the point.
Click the field for which you want the tooltip. In this case, I've chosen the Age field.
Activate the Data Validation feature by clicking DataàValidation.
When the Data Validation dialog box activates, ignore all tabs except for the Input Message tab.
Click the Input Message and enter the text you want shown in your tooltip.
Once that is done, simply click OK.
The result will be a simple and clean tooltip that activates when you click or key to the cell.
No red triangle, no jagged arrow, and no 1997-esque shadow on the box.
I've gone a step further from the past and changed the tool tip background color in Display Properties, Appearance, Advanced, to other than the old "Light Yellow", to a visually appealing "Turquoise".
Leaving the irritants aside, the comments allow much greater formating options.
You can actually change the shape of the comment, though it's not obvious how.
In Excel 2007, I've added the "Change Shape" Button on the QAT. (In the Drawing Tools | Format Tab list)
While the comment box is selected (no text cursor in it), I select from the dropdown list the shape that I want.
In Excel 2003, you do that on the Drawing toolbar \ Draw \ Change Autoshape
You can also fill it with a picture if you want in the "Format comment" dialog.
Sebastien: Sorry, but changing the shape of my comments to a cloud won't make me think they aren't ugly.
How about filling it with a photo of one of your fabulous bacon recipes
I just re-read you ideas, I tought you meant to change the shape of the comment not the annoying triangle.
I do agree that your suggestions would be greatly welcomed.
Sébastien
The worst thing about commentys IMO is that they can get munged in certain sitautions, freeze panes etc., so they are just not usable.
But doesn't your solution require that you have to select the cell? One of your big peeves with comments, but it is no different to comments.
Bob: What I meant is that if you are using your keyboard, and press the up arrow to go to a cell with comments, the comment doesn't activate.
You have to click on that cell with the mouse.
The Tootip responds to the user entering the cell with the keyboard.
Hi Mike,
Select all cells,
add custom data validation with this formula
=RAND()>0.05,set the error alert title to
'Catastrophic Failure',
and set the error message to
'Three things are certain:
Death, taxes, and lost data.
Guess which has occurred.'
Send workbook to all your friends.
I especially don't like the comments print options. They either overlay other content or they are printed at the end with reference to the cell reference. But then how many of us print with column / row headings? Debra has a little article here on how to number and list comments which makes referencing when printed a little easier: http://www.contextures.com/xlcomments03.html#Number
Also, with validation input message; I like the way you can drag the message to a different location, but I don't like that you have to close and reopen the book to reset it again. And I wish that the macro recorder would record the code when you drag it because I haven't figured out how to do that with VBA yet.
Jon
In your wish list, you have:
Ability to put hyperlinks in Comments
Interesting… intra-workbook links could be used to provide a navigation structure between cells.
Ability to put formulas in Comments
What did you have in mind here? Do you mean that the comment contains just a formula, or that you can have computed elements within the comment text? How would addressing work?
/Roy
Roy: For hyperlinks in comments, I was thinking it may be useful to apply a Comment like "Enter valid tax rate: click this link to see the valid rates"
For formulas in Comments: I was thinking more along the lines of a standard formula that would reference cells. I think it would be useful to incorporate live calculations into your comments. Example: "You have completed 12 of the 14 sections"
Ed: I've got to remember that one for April fools.
love it! using it today, and it does look a lot nicer. thanks!
Dear Bacon,
First of all, I'm a great fan of your website and add-ins. Great that via your website the knowledge of the (advanced) excel-users rises which each post.
)
Based on your post i've went looking for some appealing visuals with the comment boxes. The standard commentbox has'nt changed in 15 years……
Based on several post on blogs (see credentials) i've made a small VBA-script that replaces all the boring looking comment boxed on a sheet to something I find personally more appealing.
— C0DE—-
Sub Comments_Tom()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.Fill.ForeColor.RGB = RGB(34, 48, 108)
.Shape.Fill.Visible = msoTrue
.Shape.TextFrame.Characters.Font.Name = "Tahoma"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next 'comment
End Sub
Credentials
http://www.datapigtechnologies.com
http://www.contextures.com\xlcomments03.html
Yours sincerly and keep up those good posts/blogs,
Tom
Tom: Thanks for this. This is pretty cool.
Free Porn Search, Porn Search Engine, Adult Search, Free Galleries, Free Porn Videos, Adult Directory, Erotica Search
free porn search, porn search engine, adult search, free galleries, free porn videos, adult directory, erotica search, adult, find porn, milf, engine, cams, webcams, adult search, adult toys, webcam directory, camera, adult pictures, adult galleries, porn stuff, free pornography
http://xfdeoglss.justfree.com
Anyone know how to do things like this?