Tuesday, December 13, 2016

Storing hidden data to Excel

Assume you are creating an Excel workbook  for commercial use and you want to store some hidden data related to installation date, license period, license key, No. of licenses, Licensed users, user password etc. Also you want to have some of these data configurable like user password that can be changed later by the user. Then you have to save these data to hidden (invisible) locations within the workbook so the user can't edit or copy.  Explicitly, the workbook is only protected using three protection modes:

[1] Workbook protection
[2] Sheet protection
[3] VBA project protection

For this reason, any kind of protection schemes you will make, will always rely on one or more of the previous protection modes. The following help article from Microsoft about the document inspector shows the most possible locations for storing hidden data:

The following are some ways to store and protect these hidden data:

[1] Save value to predefined random cell. Set cell format to custom ";;;" so it will not be visible. Lock cell from being selected and protect sheet. Protect VBA project with a password

[2] Save data to a column or row, hide column or row, protect the sheet, protect VBA project with a password

[3] Insert shape, place it in a random place, set its fill and line colors to transparent (no fill), set its Alt text title or body to your value, and/or resize your shape to 0 width and 0 height. Lock shape for editing. Protect sheet, Protect VBA project as well

[4] If your sheet has shapes, then you can store data to a shape and place it in the back of another shape using the previous method

[5] Insert sheet, write your data to this sheet, then hide it. Protect workbook so it is not possible to unhide this sheet. Protect VBA project with a password

[6] Insert sheet, write your data to this sheet, then hide it by on the following ways:

(A) Programmatically using Excel VBA by setting the visibility of this sheet to "xlSheetVeryHidden".
(B) Select your sheet, select developer tab, in the "Controls" group, click "properties", and set the "Visible" property to "xlSheetVeryHidden".

You don't need to protect workbook because it will not be visible even the user unhide all sheets. It can only set back to visible programmatically from VBA. Protect VBA project with a password

[7] Insert sheet, write each character of password in a predefined random cell, hide sheet, lock workbook. Protect VBA project with a password

[8] Save password in any hidden place with complex encryption scheme of your own

[9] Save data in data validation input message title or body or error message title or body of predefined random cell

[10] Save data in screen tip of hyperlinked cell (the hyperlink is set to the cell itself) predefined random cell

[11]  Insert dialog sheet , insert form control outside the user dialog and place it in a random far place, hide the dialog sheet, protect sheet, protect workbook, and protect the VBA project.

[12] Use N formula which can stores data in cell without being seen, lock sheet, and protect the VBA project.

[13] Using work sheet custom properties.

' Save owner name
Sheets("Sheet Name").CustomProperties.Add Name:="Owner",Value:= "Shady Mohsen"
' Get owner name
Debug.print Sheets("Sheet Name").CustomProperties.Item(1).Value


Save hidden data to Excel workbook

Create commercial Excel workbooks

Save data to hidden locations inside workbook