Your own author name which appears by default when you create a comment is held as a constant edited into the macro. There is an upper limit on SchemeColor of 80 in any case. For simplicity I've started from color 40 and limited the upper end to color 52 once more than 12 authors have been found - this is partly for readability in my own scheme. This needs a loop in the Else statement of the If and an array to hold the authors' names. With a little bit of modification, you can arrange for a different colour for each author that's found. A slight variation of the macro checks the comment's Author attribute, and if it is set to "Mikki," then it makes the change: Understand that the macro will change the color if the text "Mikki" appears anywhere within the comment text. If you already have a worksheet that contains many comments, you might want to look for comments that have the text "Mikki" within them and then change the color of those comments. (The user's name is defined in the setup for Excel.) Here's an example of a short macro that will insert a comment for the selected cell and, if the user's name is "Mikki," make the background color turquoise.
The macro can not only insert the comment, but also format it according to your needs. (You've also modified how ToolTips appear in every other program on your system, as well.)Ī workaround is to use a macro to insert your comments. If you modify this, then you have modified how Excel formats your comments. You can modify this by changing the display properties for Windows, specifically the ToolTip display setting.
Instead, Excel grabs its default comment formatting from Windows itself. If you want to set a default for any comments you add in the future, Excel doesn't remember your formatting and it provides no way to make a change to the defaults. These steps are all fine and good, but they affect only the current comment.
Please copy and paste the below VBA code into the Code window. Then the Microsoft Visual Basic for Applications window pops up.
Once the comment is created, follow these steps: Right click the sheet tab which contains the shape you will hide or unhide, then click View Code from the right-clicking menu. When you add a comment to a cell in a worksheet, Excel allows you to change the formatting used for that comment. I can't help but think I'm simply taking an advantage of a bug in Excel by using something in way that it was not intended.Mikki is looking for a way to change the default color on worksheet comments so that she can tell her comments apart from comments made by other people. My hope too (perhaps a pipe dream), is that Microsoft will see this (really, I'm just a nobody blog - I'm relying on the big ones to spread the message) and consider creating a more formal method to provide both a mechanism for "highlighting" and "details on demand." As it stands, using the hyperlink formula isn't terribly intuitive and is, at times, a bit cumbersome. I'd love to take a look at anything you can send me. For example, because I use the selection_Change event for the periodic table, clicking on an already selected filter won't immediately turn it off because, well, the selection hasn't changed - no macro is fired.īut I'm sure glad to hear you are trying to incorporate the rollover into your work. You can use the MouseMove event to change the color, but since there is not a 'MouseExit' or 'MouseLeave' event I placed a transparent label BEHIND the object in question so that you have to 'MouseMove' over the transparent object to return the original color. Excel IS great! I think using a shape to handle a click (the shape would have to be semi-opaque) may prove a better technique in some circumstances. The only issue is that you are limited to a rectangle for a button or label shape.