+ Reply to Thread
Results 1 to 19 of 19

How to eliminate the red mark on the cell with comment/indicator.

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    How to eliminate the red mark on the cell with comment/indicator.

    Hello friends,

    My name is Sonny and I am very new in this forum. I am wondering if someone can help me with some problem that I try to do on my spreadsheet.
    I created many cells and I would like to add "instruction" on each cell when I move the mouse over the cell. Excel has build in feature to add the comments and indicator so you can add you text in this cell. However, the top right red indicator make the worksheet looked so crowded and distracted on the screen. Someone suggested a good ideal to use the [Data Validation Input Message] for the text, instead of comment/indicator. It helped out the look of my wroksheet but the [Data Validation Input Message] eliminate the number of text that you can type in this area (I can input more texts when using comments/indicators).
    Making my problem more simple, If anyone know how to eliminate the indicator sign on the top right corner but still allow me to see the text when I move the mouse over the cell?
    Thank you in advance for your help.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: How to eliminate the red mark on the cell with comment/indicator.

    File > Options > Advanced > Display > For Cells with Comments Show: No Comments or Indicators.

    But then you have no indication where comments are, and they don't show automatically when the cell is selected.
    Last edited by cytop; 05-13-2016 at 02:54 PM.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Full instructions with screenshots here

    http://www.officetooltips.com/excel/...ndicators.html
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: How to eliminate the red mark on the cell with comment/indicator.

    You can use code like this, in the Thisworkbook class module, to show a comment when a cell is selected
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    I already had my thought about this before working on my spreadsheet. The periodic table had 108 elements but I want my people only had special instruction on 32 of the elements. The red mark indicators look very annoy with 14 more other indicators. I rather have these cells color so people can tell the different. Thank you for your input anyway.

  6. #6
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Thank you Jeff for your help but Excel didn't include the code of "No Indicators and comments on hover".

  7. #7
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Thank you Cytop for your code. I will test your code and feedback the result on Monday. Appreciate your help again. Have a nice weekend.

  8. #8
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Hello Cytop,
    I tried the code today but it didn't remove the red mark. Thank you for your help again.

  9. #9
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Thank you 6StringJazzer for your instruction. MS Excel left out the code for "No Indicators and comments on hover".

  10. #10
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: How to eliminate the red mark on the cell with comment/indicator.

    The code does not remove the comment indicators - it just displays any existing comment when a cell is selected.

    To turn off indicators you need to set the Application options as in post #2

  11. #11
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Hi Cytop,

    I just try your suggestion but nothing happen to the worksheet or comment. When I selected the cell, the comment did not display. Please see the attachment. May be I did something wrong....
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Yes, you have the code in the wrong place... if you look in the Project Explorer (Ctrl-R in the VBA window) you'll see an item labelled 'Thisworkbook' in the Treeview. Goes in there.

    Comments seem to be handled very badly in Excel if you want anything other than Show, Show Indicators, Show Nothing. Have a look at the attached but be aware, while there is not a lot of code, it is a little more than simple VBA.

    When loaded, the comment indicators are visible.

    There is a button top left. This is just a switch to turn a Timer on and off.

    Starting the timer turns off the Comment indicators, conditionally formats all cells that have comments so the text colour is Red (just to highlight where comments are) and starts a timer. This is not an Excel OnTime but a Windows Timer which is something else altogether.

    There is a cell to input the Timer interval (how often it 'ticks'). This takes the value in milliseconds (1 second = 1000 milliseconds). Initially it is set at 2000 which seems to be a good balance but you can change it to see the effect. Changes only take effect the next time the timer is started. If set too low you will affect Excels' performance and cause flickering on screen. It will have a very noticable flicker before the performance hit though so that should help you use a sensible number.

    When the timer is on, moving the cursor over a cell with a comment will display the comment the next time the timer ticks. The Timer and Mouse moves are not synchronised so the 2000 millisecond timer interval means it can take up to 2 seconds for the comment to appear - it most likely will appear faster than that. It also means it takes up to 2 seconds for the comment to disappear after the mouse is moved away from the cell.

    Clicking the button again turns off the timer, removes the conditional formatting and displays comment indicators.

    Heed the big warning in red on the worksheet - do not unload the workbook while a timer is active. This is an example only, it's not meant to polished, working code (another thing you can't do is edit a comment while a timer is active) but if you want to try this approach, I will help (Probably means I do it! )

    But... this is horribly complicated and it suffers from a handicap - as written it can only be used on 32 bit systems. Judging by the copyright notice, this is meant for a wider audience which means more work is needed. If you can live with the restriction that a cell is selected before the comments are visible, then the 2nd attachment might be more suitable, This also includes the Conditional Formatting for cells with comments.
    Attached Files Attached Files
    Last edited by cytop; 05-18-2016 at 06:26 AM.

  13. #13
    Registered User
    Join Date
    03-20-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office Professional Plus 2007
    Posts
    25

    Re: How to eliminate the red mark on the cell with comment/indicator.

    sdjvar, Yes Data Validation will eliminate the number of text that you can type in this area depends on what selection you used. Select "Any Value" option on data validation Setting Tab to freely input on selected cells.. Then add now your Input Message.

  14. #14
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Thank you ZaLdY for your suggestion. I did use the Data Validation at my second option but it limited the amount of texts in the window and I can have more data or text with comment indicator function.

  15. #15
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Cytop, I am very impressed with your knowledge in Excel. I have to read your suggestion four times so I can understand what you try to say. Please allow me more time to get back to you. Thank you and appreciate your help again.

  16. #16
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Hello Cytop, All I can say now "You are so genius". I don't know how how you come up with the code and the timer but it worked so good.
    Today I just want to put this code in the autostart() but I couldn't make it work. I am wondering if I can make this code run automatically when I started up the workbook instead of click the Timer Switch and set the delay time when I go to the periodic table! By the way the periodic table is well design and elegant. Thank you again and again.

  17. #17
    Registered User
    Join Date
    05-16-2012
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Hello Cytop,
    Today I am able to hide the red triangle indicator by creating a macro Workbook_open(). The work book now open without the red indicator for the whole work book. It's a great news that I want to share to you and our excel user. I am also upload my sample worksheet for anyone who want to do the same. Your help and knowledge make thing easier. Please feel free to input or suggest any better way to remove the red triangle indicator after review the sample worksheet.

  18. #18
    Registered User
    Join Date
    07-11-2019
    Location
    Rotterdam, Netherlands
    MS-Off Ver
    2016
    Posts
    1

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Hi!

    Hopefully someone can help me. I wan't to try this code for the exact same problem. Unfortunately the code seems outdated and won't work in the 2016 version of Excel.

    Is there anyone who can update this code?

    Thanks in advance.

  19. #19
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: How to eliminate the red mark on the cell with comment/indicator.

    Quote Originally Posted by Bram1212 View Post
    Hi!

    Hopefully someone can help me. I wan't to try this code for the exact same problem. Unfortunately the code seems outdated and won't work in the 2016 version of Excel.

    Is there anyone who can update this code?

    Thanks in advance.
    Welcome to the Forum Bram1212!

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to REMOVE comment indicator triangles
    By Muzza86 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 11:30 AM
  2. [SOLVED] Help need to check mark + Eliminate blank cells
    By Anka in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-29-2012, 03:33 PM
  3. Transferring the Userform Textbox Values To a Single Cell As Comment Indicator
    By antzint in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2012, 04:09 PM
  4. Comment indicator in Excel 07
    By Ozwilly in forum Excel General
    Replies: 5
    Last Post: 08-04-2011, 06:47 AM
  5. [SOLVED] Changin the color of the comment indicator and back!
    By ai18ma in forum Excel General
    Replies: 3
    Last Post: 04-24-2006, 08:35 AM
  6. [SOLVED] format comment indicator
    By hmurchison in forum Excel General
    Replies: 2
    Last Post: 01-29-2006, 03:10 AM
  7. [SOLVED] I have one missing comment indicator amongst several. How can i c.
    By Banana in forum Excel General
    Replies: 10
    Last Post: 02-18-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1