Protecting Worksheet

[gaijin2004]

I protected my worksheet from the tools-protection-protect sheet menu but it seems it can be manipulated when copied into a new work sheet. I want my worksheet when copied to another worksheet, the formulas will not be copied or in other words, only the values and no formulas will be copied as if it is pasted with the command, paste special-values only. i am not well-versed with excel and i hope someone from the board will teach me how to do it. thank you very much!

 

[pmover]

i confidently state there is no technique to accomplish such a process; that is copy data from a protected worksheet and paste only the values.

something to think about...

insert another worksheet and enter formulas to obtain the information/data/values you choose for copying. hide all other worksheets & protect visible worksheet. when copying from protected worksheet to another worksheet is accomplish, the user will only see the formula "=?", where ? represents cell with calculated value. basically, create a dedicated worksheet with output values only and calcs are accomplish on another worksheet.

good luck!
-pmover

 

[IRstuff]

If all you want is to distribute previously calculated values, then you should PDF your output.

TTFN

 

[asmenut]

Short of developing VBA code, there is no way to copy a formula based cell to another worksheet without using the copy/paste special/values command.

You could create a macro and adjust the code, then create either a shortkey or an icon to accomplish the task. The problems with the macro is that that

1) The code isn't efficient (too much extraneous code)
2) If your cells aren't sequential (i.e. a1,b1,a2,b2,
etc), you might have an issue cutting and pasting.
3) If you aren't familiar enough with programming with VB
or VBA, cleaning up the code is more than a pain.

 

[Guidoo]

Anyway, it is extremely easy to unprotect any Excel spreadsheet using one of those programs that can be downloaded for free from the Internet... See for example some of the posts in thread770-85062.

The suggestion from IRstuff to make a PDF file seems a better way to protect your work.

 

[EGT01]

If you are wanting to maintain the ability to calculate with your worksheet (not just display results) you may want to consider using a spreadsheet compiler. It has been quite a few years since I first investigated that option but it seems that the product I was considering back then is still around, at least in some form.

http://the-ciba.com/vbaler/index.html

Can't say how all that compiler business works now, but back then, the idea was to take a spreadsheet and compile it into a stand-alone, executable program that didn't need having Excel, Lotus, etc. installed on your computer. As I understood back then, in its compiled form, the spreadsheet formulas would be tamper-proof.

I never did go anywhere with that option back then so I can't offer any pros/cons about that approach.

 

[Gearhead]

Wow
I just tried a new excel tool called TK Player. The equations exist in TK Solver and are distributed in a scrambled version as an Excel App via TK player. The end user can solve with his own inputs and even link other cell inputs within excel. The TK developer has the ability to let the user see the equations or just the inputs and outputs. The end user does a 1 time Free download from a website of the Player.

This new tool should solve your problem.
It is being released next Monday at

http://www.uts.com

 

[Mutt]

Can be done - in the cells where the formulae are not to be allowed to be pasted, click format - cells - protection and select locked and hidden. Then protect the worksheet. The user cannot then see the formula when the worksheet is protected and if he copy/pastes, only the values are transferred.

But as quite rightly pointed out, lot of sites out there give away Excel password cracking methods! Stop average user perhaps.

 

[d23]

gaijin2004

Just so you know Guidoo is right, there is not any protection that will protect formulas in an EXCEL spreadsheet. If you have Lotus or Quattro or about any other spreadsheet you can open any EXCEL file and see the formulas. The excel file may not work in a brand X program, but you will see the formulas.

If you want a quick stand-alone spreadsheet program that can protect the formulas try Bailer.

http://www.the-ciba.com/vbaler/

D23

 

[gaijin2004]

Thanks everyone for your comments!