Making comments very invisible.

BrighterLater

Board Regular
Joined
Sep 16, 2005
Messages
112
I was hoping to use comments, attached to a cell, as a way of storing my own properties against the cell. But .visible = false does not hide them completely; as soon as the cursor hovers over the cell the comment pops up (as designed of course). I would like to make them more invisible.

Alternatively, if anyone can suggest a way of storing information against a cell in another way then I'd be most grateful.

Here is another way but it's more complex: This would involve naming the cells, something like myprop0001, myprop0002 etc. I would then create a collection which contained object from a class module with myprop001(etc) as its name and index. I could then add whatever properties I needed to this object. However if possible I would prefer a simpler solution than this because I have round about 6000 of them and am concerned about performance.

Thank you for your assistance.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
I have to ask the obvious, why don't you store the properties in a hidden sheet(s)?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,791
Hi Mike

You can set in the options not to show the comments (in excel 2007-2010 in Options->Advanced->Display, in prior versions in Tools->Options->View). Does that help?

Remark: it helps if you post the excel version.
 

BrighterLater

Board Regular
Joined
Sep 16, 2005
Messages
112
I'm afraid it has to work in Excel 97 all the way up.

Please could you expand on the obvious. Are you suggesting a sheet that mirrors the active sheet. Please can you say how you would handle the user insterting/deleting rows
 

BrighterLater

Board Regular
Joined
Sep 16, 2005
Messages
112

ADVERTISEMENT

PGC - Thank you. I was so dissappointed by the first half of the reply, knowing it had to work in 97, that I didn't get round to reading the 2nd part! Doh!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
I thought pgc01 had answered your question but then you PM'ed me to expand on my answer.

Please could you expand on the obvious. Are you suggesting a sheet that mirrors the active sheet.
To be honest, I hadn't really thought it through. Yes, I was suggesting that a hidden sheet "mirror" a user's visible sheet. It's not a perfect solution.

Please can you say how you would handle the user insterting/deleting rows.
It depends on what your project is for. You could password Protect the user's sheet to prohibit them from inserting new cells. You could write a macro to insert specific rows or columns for the user and it would insert the same cells on the hidden sheet. There are probably other ideas depending on the requirements of your project.
 

BrighterLater

Board Regular
Joined
Sep 16, 2005
Messages
112

ADVERTISEMENT

Thank you pgc01 and AlphaFrog.

The advantage of using Excel Addins is its great disadvantage too, the user can continue using Excel as normal on top of your application. pgc01 aswered my first question about hiding Comments and Alpha frog gave me an answer for an alternative solution.

The solution I'm favouring at the minute is to name the cells that I'm interested in and keep another sheet with two columns: one to contain the names and the other one for their properties. It has the advantage that names deal automatically with row insertion. In the application it should be clear to them not to delete the row but if they do then I know they've done it (cause the name has dissappeared.) Then if I use an idea, similar to AlphaFrog's, I can restore the deleted row by keeping a backup mirror sheet.

Thank you again - Mike
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Maybe there is some other place you can hide the data.

Such as, what about as the cell's data validation error alert message? So, from a cell, ALT-D-L, third tab, error alert. Don't apply any data validation, so the message will not be required to show.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,402
Messages
5,511,139
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top