Hidden Formulas (Office 97)

[braddy60]

Hi

I have a spreadsheet with prices in Euros I use a formula to convert the cells to sterling to issue to salesmen. I then lock the cells and hide the formulas, protect the sheet so that they cannot read the rate. But of course if they copy and paste into another worksheet the formulas become readable again, is there any way to prevent this so they are never viewable. Apart from converting the formulas to values, which is slightly complicated because of merged cells.

Thanks again

[smerdonk]

My thought is to create your own function, put it in a module, and protect the module (ensure you Lock the project for viewing) with a password. Even if the workbook is Saved As, the password is preserved - as far as I can tell, anyway.

To adjust the rate, just edit it right in the function, since YOU will know the password.

Have a look at the attached workbook - the password for the module is <font color=blue>test</font color=blue>.

[smerdonk]

Here's the workbook:

[sdckapr]

You can create a hidden name, for example (I just put a number in I do not know the rate) with:

ActiveWorkbook.Names.Add Name:="EuroToSterlingRate", RefersToR1C1:="=1.234", Visible:=False

Then use the "rate name" in calculations.

[JohnBF]

If I recall correctly, if you also protect the workbook (and presumably all the worksheets in it), the -values- can be copied to another workbook, but not the formulas.

[braddy60]

Hi

There's some interesting ideas here I wil give them a try

Thanks to all who replied

[BRoby]

The other way of course would be to create your final worksheet with all the calculations done and then Copy the data over to another worksheet using paste special values.
This way there is only values and no possibility of anyone seeing or recovering your formulas, of course you have to distribute the later worksheet.
If you record a macro as you do this you could automate the process of producing the distribution sheet quite easily.
Cheers
Brian.

[macropod]

Hi braddy60,

Regarding your statement:
"I then lock the cells and hide the formulas, protect the sheet so that they cannot read the rate. But of course if they copy and paste into another worksheet the formulas become readable again".

If you do "lock the cells and hide the formulas, protect the sheet" , then copying the cells that are locked & hidden on the protected sheet does not copy the formulae - only their formats and the values resulting from whatever calculations they had done can be copied and pasted elsewhere.Having said that, though, it would usually be a trivial exercise to reverse engineer a currency conversion.

Cheers

[braddy60]

Hi

This sounds like the best idea for what I want.

Thanks a lot

[jstevens]

Have you tried making one sheet "very hidden". The user can not access a "very hidden" sheet by trying to unhide it nor are they capable of printing the sheet. The code would be:

Sheets("Sheet2").Visible = xlVeryHidden

to make it visible again use: Sheets("Sheet2").Visible = true

[braddy60]

Hi All

Thanks to all who responded, I have enough to be going on with.

I'm grateful

[braddy60]

Hi

This looks interesting I will give it a try.

Thanks very much.