How to save an extra piece of (hidden) information using a cell in excel

Question

I am writing a work application that compiles the entire mess of different tables into reports for a specific work site. I have quite a few sheets with a number of indicators on each. Each metric on each sheet may need to be compiled from a different worksheet and will be found on this sheet by looking at the keyword.

Another problem is that some of the wordings of these tables change periodically to better reflect industry standards, so I can’t just search for keywords to search.

So, I’m looking for a way to store metadata with a cell that I can hide from users so that they don’t accidentally delete it, but can easily access VBA to change if necessary (I would write a procedure for this if necessary).

The study says that I can use comments (although I'm not sure if I can access them from vba and I would like them hidden) or a hidden worksheet to reflect each of the sheets that I use with information in this cell on this sheet. I will probably go with the latter, but this is not ideal.

Any other ideas?

Edit for clarity: I need a line associated with a single cell that will tell me how to find the relevant data. Example: "Look in book 1 -% - Look for this text."

The VeryHidden Attribute mentioned by @Andrew may be the best way, since I don't think there is a way to bind a variable to a cell.

Answer 1

One of my favorite tricks is to use the N() function.

N() does not convert a number to a number, but if you are its root text, it always returns 0. When I need to add a note to a cell, I will add something like:

 =..... +N("This is to calculate blah blah blah") 

As long as adding 0 to the value doesn't hurt, it works well.

Answer 2

What I did in the past when you need to store data for the application, but which should not be accessible to users, is to use a sheet with the visibility set to VeryHidden .

It may also be possible to create an Excel Add-in (XLA) that can be independent of user data and therefore can be updated separately if the rules need to be changed; - although in the Updates book, you can use VBA to replace all the VBA modules in the book "data". I have to see if I can find the code I wrote to do this.

Answer 3

You can use a similar trick for text.

You can use:

= "Display Value" and left ("This is to calculate ...", 0)

OR

= SELECT (1, "Display value", "This needs to be calculated ...")

Answer 4

Each spreadsheet must have labels or headers, or at least some description fields. If this is true, there is a trick that you can hide the value in one of these cells and no one will know. This is how you do it.

• Enter a value in the cell with =N() . For instance:

=N("Apple, Google, Facebook, Microsoft") .

2. Go to Format Cells > Custom : enter the text you want to display for the title / label / description. For instance:

"Header Name" .

As you can see the attached image, which I was able to enter into the cell, but shows something else. And as a bonus, you can use this syntax below to get the formula / text from VBA for your program:

Range("A1").Formula

Hope this helps.

Answer 5

I ran into this problem and found several possible solutions depending on the need.

Save data to Hyperlink

You can store data in hyperlinks, for example, in the link itself or in the tooltip for the link. The advantage of this method is that the data is tied to the cell itself, and not to the address. This means that if, for example, someone sorts cells, followed by hidden data. You can also catch the HyperlinkClick event if you want, but none of these hyperlinks will go anywhere, so this probably doesn't matter.

Using ScreenTip

 ' Write hidden data to a cell Public Sub WriteData(ByVal Cell As Range, ByVal Data As String) Cell.Hyperlinks.Delete Cell.Hyperlinks.Add Cell, Address:="#", ScreenTip:=Data ' Set standard formatting With Cell.Font .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub ' Read hidden data from a cell Public Function ReadData(ByVal Cell As Range) As String If Not CellContainsData(Cell) Then Exit Function ReadData = Cell.Hyperlinks(1).ScreenTip End Function Private Function CellContainsData(ByVal Cell As Range) As Boolean CellContainsData = Cell.Hyperlinks.Count > 0 End Function 

Using Address

This probably has some restrictions on the look of the lines that you can save, as they should be somewhat valid links. For example, it should not contain spaces, but you, of course, can use a code (for example, \20 ) to represent a space and decode it later.

Disclaimer : I'm not really quite sure what this does, I tried to find a way to create a valid link that has not gone away, and this is what I came up with. This violates Excel somewhat, since you can no longer edit the hyperlink using the Excel GUI, and clicking on it does not raise the FollowHyperlink event. If you just set ?Data as the address, Excel will clear it the next time you click.

 ' Write hidden data to a cell Public Sub WriteData(ByVal Cell As Range, ByVal Data As String) Cell.Hyperlinks.Delete Cell.Hyperlinks.Add Cell, Address:="//?" & Data, ScreenTip:="Nothing suspicious here." ' Set standard formatting With Cell.Font .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub ' Read hidden data from a cell Public Function ReadData(ByVal Cell As Range) As String If Not CellContainsData(Cell) Then Exit Function Dim Data As String Data = Cell.Hyperlinks(1).Address ReadData = Right$(Data, Len(Data) - 3) End Function Private Function CellContainsData(ByVal Cell As Range) As Boolean If Cell.Hyperlinks.Count < 1 Then Exit Function Dim Data As String Data = Cell.Hyperlinks(1).Address ' Check that the cell has the correct prefix If Len$(Data) < 3 Then Exit Function If Left$(Data, 3) <> "\\?" Then Exit Function CellContainsData = True End Function 

Save data to Validation

It is also possible to store data in a cell check. This, however, does not work if, for example, someone sorts the cells.

 ' Write hidden data to a cell Public Sub WriteData(ByVal Cell As Range, ByVal Data As String) With Cell.Validation ' Remove previous validation .Delete ' Write data on a specific format so you know it was you. .Add xlValidateCustom, Formula1:="""@" & Data & """" ' Hide it as well as possible .InCellDropdown = False .ShowInput = False .ShowError = False End With End Sub ' Read hidden data from a cell Public Function ReadData(ByVal Cell As Range) As String If Not CellContainsData(Cell) Then Exit Function Dim Data As String Data = Cell.Validation.Formula1 ReadData = Mid$(Data, 3, Len(Data) - 3) End Function Private Function CellContainsData(ByVal Cell As Range) As Boolean On Error GoTo InvalidValidation If Cell.Validation.Type <> xlValidateCustom Then Exit Function Dim Data As String Data = Cell.Validation.Formula1 ' Check that the data is on your specific format If Left$(Data, 2) <> """@" Then Exit Function If Right$(Data, 1) <> """" Then Exit Function CellContainsData = True InvalidValidation: Exit Function End Function