I encountered this exact problem and found a couple of possible solutions depending on need.
Save data in 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. Meaning that if e.g. someone sorts the cells the hidden data follows. You could also catch the HyperlinkClick event if you wished, but none of these hyperlinks go anywhere, so it 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 kind of string you can save as they have to somewhat be valid links. For example it cannot have any spaces in them, but you could of course use a code (like \20) to represent a space and decode it later.
Disclaimer: I am actually not entirely sure what this does, I was trying to find a way to create a valid link that didn't go anywhere and this is what I came up with. It somewhat breaks excel as you can no longer edit the hyperlink with the Excel GUI, and clicking it doesn't trigger a FollowHyperlink event. If you simply set ?Data as the address then Excel will clear it the next time anyone clicks it.
' 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 in Validation
It is also possible to store data in the validation of a cell. This however does not work if e.g. 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
VeryHiddenso they can only be unhidden with VBA. Could you get away with one, with different metadata in different ranges on that sheet? – CresolMirroreach worksheet to this worksheet like you mentioned above. As for the comments, A comment can be handled from vba but then those can be deleted from the worksheet if the worksheet is not protected. If it is couple of cells from each sheet then using 1 hidden worksheet will definitely help. – Archicarp