Does anyone have a "save" solution?

  • Thread starter Thread starter Zoe Wolf
  • Start date Start date
Z

Zoe Wolf

Greetings,

I've hunted around and couldn't find a solution :( Perhaps someone ha
some wisdom for me?

As a gift, I am creating some custom templates to assist a friend. Th
templates will be saved into files that must eventually be given t
others for whom work was done (i.e. an electronic saved file, instea
of paper files.) The content of the file must be viewable an
reprintable.

The dilemma...I do not want my custom work given away or reused b
anyone but my friend. The protection options I've explored and th
"read only" status still permits the saved files to be used/edited t
create other new files...so that won't work.

My friend is not terribly sophisticated with computer use so th
solution for saving these files must be easy. I'm rather new to Exce
(no VBA experience.) I've thought of saving the files to an html (n
bueno) or some other format like Adobe, but my friend does not hav
Adobe.

What am I missing for an easy solution? Anyone have a good idea?

Thanks in advance...
 
Once you put your work into the workbook and then that workbook is shared, then
there's really not much you can do to stop the use by others.

If your work consists of macro utilities to make your friend's life a little
easier, maybe you could put those into a separate workbook, save it as an addin
and divorce it from the template.

Those addin utilities would only be accessible to those people with the addin.

But if your work consists of formulas and the like, then I think excel may not
be the secure tool for you.
 
I agree with Dave, an addin is best. There are ways to make it difficult
to see the fomulas, however.

Using Format > Cells > Protection Tab....
Make all cells where the user needs to enter values Unlocked
Make all secure formula cells Locked and Hidden. The Hidden is essential!
Hidden will show cell values, but not the formula behind it. It also hides
the formula from the VBA range.formula property. Without the Hidden, then
a formula can be had simply with the following User Defined function:
Function wwFormulaOfCell(rng1 As Range) As Variant
wwFormulaOfCell = rng1.Formula
End Function

Cell B9: =wwFormulaOfCell(H9)
See note 1 below:

Use Tools > Protection > Protect Sheet .
For good measure (not strictly necessary) turn off user can select locked
cells.
Give it a BIG, LONG password.

For extra measure, put the bulk of your formulas on another sheet. Hide
the sheet. Then protect the workbook structure.

The bad news is that cracking Excel Passwords is embarrasingly easy. So an
Addin is still the best approach.

So don't forget to put a Copyright protection on your work. Personally, I
like the philosophy of www.creativecommons.org. I like to think of their
idea as an "Object Oriented" legal agreement. You can tell from the type
and version exactly what it covers without having to read thousands of
words.
I routinely put a http://creativecommons.org/licenses/by-nc-sa/1.0/
copyright in my software. I may be wrong, but I like the terms better than
a GPL.

Note 1: I discovered a bug in the selection protection. I have a
worksheet where I have allowed the user to select only unlocked cells. In
Tools, Options, Edit, I clear the "Edit Directly in Cell". I like the
ability to shift-dblclick a formula and have it select the precident cells.
Guess what! Enter a formula in an unlocked cell (say B9), reference a
locked cell (say H9). Double click B9. H9 will be selected. As long
as you have used the Hidden Protection, your formula is still safe ---
relatively save, anyway.

Stephen Rasey
WiserWays, LLC
Houston, TX
Leader of HAL-PC Excel SIG -- www.excelsig.org
 
Hi Zoe,
Protecting your intellectual property.

Usually if someone looks at your work they can figure out how
to do it. If they can't figure it out -- how would they know that
it is correct.

You indicate that this is for a friend -- I guess that means they
aren't paying for it. Because someone paying for work isn't
likely to be happy without the ability to modify it. If work is
done under contract then they usually own it outright, and you
may be able to get it into the contract that you can also sell the
same solution. If work was done for your employer then
you have no rights to your work, it would be owned by
your employer and withholding or selling anything would be theft.

If someone is working with the spreadsheet then there
really isn't anything that you can do protect everything
from all users. But you mention converting to HTML
or to Adobe PDF files.

If you convert to HTML with
Excel then everything from Excel goes into HTML and
it can be copied back to Excel and be complete --
this is called round tripping. Start with an Office
program and convert to other Office programs and
eventually back to where you started -- that is why it
is called round tripping.

If you convert to PDF then I assume you have that
ability to create a PDF file. The person reading the output
can use the free Adobe Acrobat Reader. If you don't
have the ability to convert to PDF but are just thinking
about it there are free converters around. See
http://www.mvps.org/dmcritchie/excel/pdf.htm
But it appears that the user needs access to the
Excel files.

An addin written in C++ would protect your code
better than anything written in VBA if macros and
user functions were involved.
 
Hi Dave,

I wanted to thank you for taking time to give a response in my searc
for a solution.

My spreadsheet only holds formulas, though some of them go beyond th
"math" - doing some nice headstands. I've never done macros in Excel
nor an add-in...in fact I've rarely ever done macros in an
spreadsheet. I'm sure macros/add-ins could perform some amazing thing
- but I'd have a bit of a learning curve to figure out how to d
either.

I think the bottomline is fairly much what you have mentioned...sharin
the files via spreadsheet would not be the secure thing I would need.
Thanks for your honesty and assistance.
 
Hiya Stephen,

Thanks for the detailed response, it is ALWAYS helpful to have "clear
instructions...detailed. I've been giving "free" online support fo
another software since 1995...and have long felt that users deserve th
benefit of the doubt (we never know their level of experience.) I'v
always felt "good" when a user can walk away from one of my response
"not feeling like a dodo." In that respect...I certainly enjoyed you
response.

Thanks for pointing out that bug...woah! So much for protections tha
are not protections at all. Security issues are one of my pe
peeves...

I suppose that if I continue to work much with Excel in th
future...I'd better learn about the "add-in". The tip abou
referencing formulas in another spreadsheet is good too! Thanks
bunch...

In the meanwhile...I'm thinking that the best way to protect my har
work is to provide "snapshots" of that which it produces.

Thanks a ton,
 
Hiya David,

Wanted to thank you for your stimulating input!

Correct, no one is paying to have these templates created and that is
the point.

You wrote: "Usually if someone looks at your work they can figure out
how to do it. If they can't figure it out -- how would they know that
it is correct."

Hmmm...well I admit that there are times that I will give myself the
brain exercise to check on some of that which Quicken and Turbo Tax
produces, but I sure don't think I could have produced that code.
Fortunately, those few who I have actually let pay me for templates
were happy to do so due to the "sheer relief" my work gave them. I'm
betting that those who pay any of us who do this type of work are happy
as clams to do so! :)

Good point about converting to html. Woah, I sure didn't realize that
the underlying formulas went with the conversion. Thanks for that
heads up!

AND...thanks for the pointer to your PDF page and pdf995. Guess I'm
going to have to try that...sure looks like a good product for the
price.

Thanks again for the hand.

Z
 
Back
Top