Password protect hidden cells

Hello,

I have a customer who has sale people that have product spreadsheets for
pricing. They have wholesale and retail pricing on the same sheet. The
wholesale prices they hide and password protect, however, the problem is you
can open the spreadsheet in read only mode and then save a copy of it. Then
open the saved copy and the password protection is then removed.

I'm trying to find a way to prevent this. However in searching it appears
that the password is pretty much insecure any way. So my thoughts are using
MS office tools for VS and creating an Excel Project and hard coding the
password into the document for them.

First of all if anybody has a better suggestion on how to secure these
hidden cells where only sales person have access to them, I would love to
hear it. Secondly if building and compiling the app is the best way, does
anybody have a sample for VB.Net?

Thanks.

kris

Update:

What I've found is the password is staying in place. However if you select
all the cells in the protected document and the copy them and past them into
a different workbook. All the hidden cells are copied and displayed in the
new work book. So it appears I'm looking for a way to disable the copy/cut
functionality from the worksheets.

Any ideas?



"kfrost" wrote:

> Hello,
>
> I have a customer who has sale people that have product spreadsheets for
> pricing. They have wholesale and retail pricing on the same sheet. The
> wholesale prices they hide and password protect, however, the problem is you
> can open the spreadsheet in read only mode and then save a copy of it. Then
> open the saved copy and the password protection is then removed.
>
> I'm trying to find a way to prevent this. However in searching it appears
> that the password is pretty much insecure any way. So my thoughts are using
> MS office tools for VS and creating an Excel Project and hard coding the
> password into the document for them.
>
> First of all if anybody has a better suggestion on how to secure these
> hidden cells where only sales person have access to them, I would love to
> hear it. Secondly if building and compiling the app is the best way, does
> anybody have a sample for VB.Net?
>
> Thanks.
>
> kris
>
>

It's not possible. Any solution that prevents copying would rely on macros.
But a user could thwart that by disabling macros. Excel is simply not a
good repository of sensitive information.

--
Jim
"kfrost" <[email protected]> wrote in message
news:[email protected]...
| Update:
|
| What I've found is the password is staying in place. However if you
select
| all the cells in the protected document and the copy them and past them
into
| a different workbook. All the hidden cells are copied and displayed in
the
| new work book. So it appears I'm looking for a way to disable the
copy/cut
| functionality from the worksheets.
|
| Any ideas?
|
|
|
| "kfrost" wrote:
|
| > Hello,
| >
| > I have a customer who has sale people that have product spreadsheets for
| > pricing. They have wholesale and retail pricing on the same sheet. The
| > wholesale prices they hide and password protect, however, the problem is
you
| > can open the spreadsheet in read only mode and then save a copy of it.
Then
| > open the saved copy and the password protection is then removed.
| >
| > I'm trying to find a way to prevent this. However in searching it
appears
| > that the password is pretty much insecure any way. So my thoughts are
using
| > MS office tools for VS and creating an Excel Project and hard coding the
| > password into the document for them.
| >
| > First of all if anybody has a better suggestion on how to secure these
| > hidden cells where only sales person have access to them, I would love
to
| > hear it. Secondly if building and compiling the app is the best way,
does
| > anybody have a sample for VB.Net?
| >
| > Thanks.
| >
| > kris
| >
| >

Thanks you Jim,

Is there something I'm missing then. Any idea as to why you can take a
document that is password protected with hidden cells/columns or rows and
copy the entire document to a new workbook and where all the hidden,
"protected" cells are then displayed?

How do you prevent this? I would assume it would be possible because if it
wasn't why would they waste their time creating the password/proctection
functionality.

If not, what about building a Excel spreadsheet in Visual Studio with the
office tool kit. Could you hard code and app to gain this functionality?

Thanks.

kris



"Jim Rech" wrote:

> It's not possible. Any solution that prevents copying would rely on macros.
> But a user could thwart that by disabling macros. Excel is simply not a
> good repository of sensitive information.
>
> --
> Jim
> "kfrost" <[email protected]> wrote in message
> news:[email protected]...
> | Update:
> |
> | What I've found is the password is staying in place. However if you
> select
> | all the cells in the protected document and the copy them and past them
> into
> | a different workbook. All the hidden cells are copied and displayed in
> the
> | new work book. So it appears I'm looking for a way to disable the
> copy/cut
> | functionality from the worksheets.
> |
> | Any ideas?
> |
> |
> |
> | "kfrost" wrote:
> |
> | > Hello,
> | >
> | > I have a customer who has sale people that have product spreadsheets for
> | > pricing. They have wholesale and retail pricing on the same sheet. The
> | > wholesale prices they hide and password protect, however, the problem is
> you
> | > can open the spreadsheet in read only mode and then save a copy of it.
> Then
> | > open the saved copy and the password protection is then removed.
> | >
> | > I'm trying to find a way to prevent this. However in searching it
> appears
> | > that the password is pretty much insecure any way. So my thoughts are
> using
> | > MS office tools for VS and creating an Excel Project and hard coding the
> | > password into the document for them.
> | >
> | > First of all if anybody has a better suggestion on how to secure these
> | > hidden cells where only sales person have access to them, I would love
> to
> | > hear it. Secondly if building and compiling the app is the best way,
> does
> | > anybody have a sample for VB.Net?
> | >
> | > Thanks.
> | >
> | > kris
> | >
> | >
>
>
>

>>Is there something I'm missing then.

I think so. I'd say that protection in general is there to keep people from
inadvertently mucking with your workbook. Say you set it up in a certain
way to get information or to help users calculate a result. You hide stuff
more as a matter of cosmetics than to keep it truly secret. It was never
intended as security.

Excel is Excel. If you automate an Excel workbook from another app or from
a DLL you still have a workbook that can be opened independently and
'explored'.

On the other hand if the sensitive data/calculations never gets to an Excel
workbook then that issue goes away. Say all of that was in a DLL and that
Excel is used only as a presentation vehicle of the final results. That's
the only answer I can think of but then you lose Excel's calculational
power. And you have to know how to write such a DLL.

--
Jim
"kfrost" <[email protected]> wrote in message
news:[email protected]...
| Thanks you Jim,
|
| Is there something I'm missing then. Any idea as to why you can take a
| document that is password protected with hidden cells/columns or rows and
| copy the entire document to a new workbook and where all the hidden,
| "protected" cells are then displayed?
|
| How do you prevent this? I would assume it would be possible because if
it
| wasn't why would they waste their time creating the password/proctection
| functionality.
|
| If not, what about building a Excel spreadsheet in Visual Studio with the
| office tool kit. Could you hard code and app to gain this functionality?
|
| Thanks.
|
| kris
|
|
|
| "Jim Rech" wrote:
|
| > It's not possible. Any solution that prevents copying would rely on
macros.
| > But a user could thwart that by disabling macros. Excel is simply not a
| > good repository of sensitive information.
| >
| > --
| > Jim
| > "kfrost" <[email protected]> wrote in message
| > news:[email protected]...
| > | Update:
| > |
| > | What I've found is the password is staying in place. However if you
| > select
| > | all the cells in the protected document and the copy them and past
them
| > into
| > | a different workbook. All the hidden cells are copied and displayed
in
| > the
| > | new work book. So it appears I'm looking for a way to disable the
| > copy/cut
| > | functionality from the worksheets.
| > |
| > | Any ideas?
| > |
| > |
| > |
| > | "kfrost" wrote:
| > |
| > | > Hello,
| > | >
| > | > I have a customer who has sale people that have product spreadsheets
for
| > | > pricing. They have wholesale and retail pricing on the same sheet.
The
| > | > wholesale prices they hide and password protect, however, the
problem is
| > you
| > | > can open the spreadsheet in read only mode and then save a copy of
it.
| > Then
| > | > open the saved copy and the password protection is then removed.
| > | >
| > | > I'm trying to find a way to prevent this. However in searching it
| > appears
| > | > that the password is pretty much insecure any way. So my thoughts
are
| > using
| > | > MS office tools for VS and creating an Excel Project and hard coding
the
| > | > password into the document for them.
| > | >
| > | > First of all if anybody has a better suggestion on how to secure
these
| > | > hidden cells where only sales person have access to them, I would
love
| > to
| > | > hear it. Secondly if building and compiling the app is the best
way,
| > does
| > | > anybody have a sample for VB.Net?
| > | >
| > | > Thanks.
| > | >
| > | > kris
| > | >
| > | >
| >
| >
| >

Thanks for the input Jim, if what you say is correct then I have an issue
with the wording of the help in Excel and the way they describe security and
protection. With what you've stated the product doesn't live up to what is
described in the help files. Because they talk about security/protection,
strong passwords etc so nobody can access the protected items. But if you
can just copy and paste to another worksheet that's a security breach or flaw.

As a matter of fact the product should be advertised as you have mentioned
and the word security should be removed all together from the documentation.

Again, I appreciate your help. I'm taking this up with the Office team
directly to get an official word before scrapping excel for something else.

Thank you.



"Jim Rech" wrote:

> >>Is there something I'm missing then.
>
> I think so. I'd say that protection in general is there to keep people from
> inadvertently mucking with your workbook. Say you set it up in a certain
> way to get information or to help users calculate a result. You hide stuff
> more as a matter of cosmetics than to keep it truly secret. It was never
> intended as security.
>
> Excel is Excel. If you automate an Excel workbook from another app or from
> a DLL you still have a workbook that can be opened independently and
> 'explored'.
>
> On the other hand if the sensitive data/calculations never gets to an Excel
> workbook then that issue goes away. Say all of that was in a DLL and that
> Excel is used only as a presentation vehicle of the final results. That's
> the only answer I can think of but then you lose Excel's calculational
> power. And you have to know how to write such a DLL.
>
> --
> Jim
> "kfrost" <[email protected]> wrote in message
> news:[email protected]...
> | Thanks you Jim,
> |
> | Is there something I'm missing then. Any idea as to why you can take a
> | document that is password protected with hidden cells/columns or rows and
> | copy the entire document to a new workbook and where all the hidden,
> | "protected" cells are then displayed?
> |
> | How do you prevent this? I would assume it would be possible because if
> it
> | wasn't why would they waste their time creating the password/proctection
> | functionality.
> |
> | If not, what about building a Excel spreadsheet in Visual Studio with the
> | office tool kit. Could you hard code and app to gain this functionality?
> |
> | Thanks.
> |
> | kris
> |
> |
> |
> | "Jim Rech" wrote:
> |
> | > It's not possible. Any solution that prevents copying would rely on
> macros.
> | > But a user could thwart that by disabling macros. Excel is simply not a
> | > good repository of sensitive information.
> | >
> | > --
> | > Jim
> | > "kfrost" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | Update:
> | > |
> | > | What I've found is the password is staying in place. However if you
> | > select
> | > | all the cells in the protected document and the copy them and past
> them
> | > into
> | > | a different workbook. All the hidden cells are copied and displayed
> in
> | > the
> | > | new work book. So it appears I'm looking for a way to disable the
> | > copy/cut
> | > | functionality from the worksheets.
> | > |
> | > | Any ideas?
> | > |
> | > |
> | > |
> | > | "kfrost" wrote:
> | > |
> | > | > Hello,
> | > | >
> | > | > I have a customer who has sale people that have product spreadsheets
> for
> | > | > pricing. They have wholesale and retail pricing on the same sheet.
> The
> | > | > wholesale prices they hide and password protect, however, the
> problem is
> | > you
> | > | > can open the spreadsheet in read only mode and then save a copy of
> it.
> | > Then
> | > | > open the saved copy and the password protection is then removed.
> | > | >
> | > | > I'm trying to find a way to prevent this. However in searching it
> | > appears
> | > | > that the password is pretty much insecure any way. So my thoughts
> are
> | > using
> | > | > MS office tools for VS and creating an Excel Project and hard coding
> the
> | > | > password into the document for them.
> | > | >
> | > | > First of all if anybody has a better suggestion on how to secure
> these
> | > | > hidden cells where only sales person have access to them, I would
> love
> | > to
> | > | > hear it. Secondly if building and compiling the app is the best
> way,
> | > does
> | > | > anybody have a sample for VB.Net?
> | > | >
> | > | > Thanks.
> | > | >
> | > | > kris
> | > | >
> | > | >
> | >
> | >
> | >
>
>
>

Hello,

I wanted to follow up on this. I had another engineer from MS call back and
there is a way to prevent the hidden cells or any cell for that matter from
being copied in excel. To do so you click on Tools, Protection, Protect Sheet

There is a list of check boxes in the Protect Sheet window under "Allow all
users of this worksheet to:"

Uncheck "Select locked cells" and "Select unlocked cells" and then ok. Then
users will not be able to select any cells in the worksheet to copy or paste.

I understand that the password technology is still lacking but for general
users that wouldn't look for a way to hack the sheet.

"kfrost" wrote:

> Hello,
>
> I have a customer who has sale people that have product spreadsheets for
> pricing. They have wholesale and retail pricing on the same sheet. The
> wholesale prices they hide and password protect, however, the problem is you
> can open the spreadsheet in read only mode and then save a copy of it. Then
> open the saved copy and the password protection is then removed.
>
> I'm trying to find a way to prevent this. However in searching it appears
> that the password is pretty much insecure any way. So my thoughts are using
> MS office tools for VS and creating an Excel Project and hard coding the
> password into the document for them.
>
> First of all if anybody has a better suggestion on how to secure these
> hidden cells where only sales person have access to them, I would love to
> hear it. Secondly if building and compiling the app is the best way, does
> anybody have a sample for VB.Net?
>
> Thanks.
>
> kris
>
>

Assume the sheet name is Sheet1

in another sheet, go to A1

put in the formula

=Sheet1!A1

then drag fill down and across.

This won't pickup formulas of course.



--
Regards,
Tom Ogilvy


"kfrost" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I wanted to follow up on this. I had another engineer from MS call back
and
> there is a way to prevent the hidden cells or any cell for that matter
from
> being copied in excel. To do so you click on Tools, Protection, Protect
Sheet
>
> There is a list of check boxes in the Protect Sheet window under "Allow
all
> users of this worksheet to:"
>
> Uncheck "Select locked cells" and "Select unlocked cells" and then ok.
Then
> users will not be able to select any cells in the worksheet to copy or
paste.
>
> I understand that the password technology is still lacking but for general
> users that wouldn't look for a way to hack the sheet.
>
> "kfrost" wrote:
>
> > Hello,
> >
> > I have a customer who has sale people that have product spreadsheets for
> > pricing. They have wholesale and retail pricing on the same sheet. The
> > wholesale prices they hide and password protect, however, the problem is
you
> > can open the spreadsheet in read only mode and then save a copy of it.
Then
> > open the saved copy and the password protection is then removed.
> >
> > I'm trying to find a way to prevent this. However in searching it
appears
> > that the password is pretty much insecure any way. So my thoughts are
using
> > MS office tools for VS and creating an Excel Project and hard coding the
> > password into the document for them.
> >
> > First of all if anybody has a better suggestion on how to secure these
> > hidden cells where only sales person have access to them, I would love
to
> > hear it. Secondly if building and compiling the app is the best way,
does
> > anybody have a sample for VB.Net?
> >
> > Thanks.
> >
> > kris
> >
> >