can you encrypt an excel cell

[christopher ward]

ok experts

i have written a new system that creates a worksheet called Data - the
contents of data are created from forms and VBA - my question is : can the
contents of a range be given a DISPLAY ENCRYPTION so that a user can not go
to the cells and see the contents

I should add that although this appears a strange request the owner of the
vba system does not want to reveal how the system works so thus hiding data
is a good idea - i guess he does not want his ideas copied

can this be done ???? or how should i address this issue ????

 

[christopher ward]

i also do not want to HIDE the sheet due to the code system that sits around
the system

 

[dominicb]

Good evening Christopher Ward

Do I understand that the workbook creator wants to hide formulae, but
allow the results to be seen?

Select all the cells that contain formulae to remain unseen.
Press Ctrl + 1, go to the protection tab and check the box marked
hidden.
Set the protection with Tools > Protection > Protect Sheet.

This will also lock all cells marked as Locked, so you mioght want to
clear the locked checkbox against all the cells that are to have an
entry before you do the last step.

HTH

DominicB

 

[christopher ward]

not quite this but good thoughts thank you for your efforts

my user creates a sheet called data with about 25 cols of data - he wants to
make sure that if you use excel to view the data it cannot be understood - a
sort of encryption, at the moment no cells are locked and they contain mainly
text data and very few formulas . I guess its a form of visual display change
i want

 

[Tom Ogilvy]

I assume the data is on the sheet so it can be used by formulas in the
workbook. If so, there is no built in way to support what you want. You
could build a macro to encrypt the data in the cells, then build a UDF that
is used inside all your formula. Something of the form:

=sum(MyDecryptUDF(SecretDataSheet!A1:A10))

 

[christopher ward]

kind regards i will see what i can do - thank you for your time

 

[GB_Ronin]

Hi Christopher,

Maybe I haven't understood the problem correctly but have you
considered changing the format of the cells so that the text is the
same colour as the cell background colour e.g. white on white in those
columns or cells where the content should not be displayed.

Then protect the sheet so that the format cannot be changed back
again.

Presumably the user that creates the data would be able to unprotect
the sheet to add new data if he needs to.

Also removing the formula bar will stop other users from seeing the
content of individual cells.

Hope that may help,

Tim

 

[christopher ward]

thank you a good and simple idea - also i assume any vba code can still pick
up as it did as it is only a colour system and not changing the data

 

[GB_Ronin]

Hi again,

i assume any vba code can still pick
up as it did as it is only a colour system and not changing the data

That's correct, the VBA code will have no problem reading the values in
each cell formated in this manner.

Tim