Making comments very invisible.

[BrighterLater]

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.

 

[AlphaFrog]

I have to ask the obvious, why don't you store the properties in a hidden sheet(s)?

 

[pgc01]

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]

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]

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]

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]

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]

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.

 

[BrighterLater]

Thanks - Fazza - Great Idea

 

[Fazza]