You want to keep the formulas hidden from the user, but allow them to
protect/unprotect the worksheet whenever they want???

I don't think you can do that using builtin features.

But you could replace your worksheet formulas with UserDefined Functions
(rewrite them in VBA) and then the user can see your function and you'll still
have some secrecy:

=myTopsecretformula(a1,b1,c1)

wouldn't mean much to me.

On the other hand, the really interested could buy a password cracker that will
unprotect your code and then they could see it.

Dag Johansen wrote:

Hi,

is it possible to use protection at the Range level rather
than the Sheet?

I would like a module to lock a cell and hide it's
formula, but at the same time the end user should be able
to protect the sheet/book using a password of her own
choice. Can it be done?

Hope for a positive answer! Meanwhile, happy coding.

Dag Johansen

--

Dave Peterson

Hi Dave,

thanks for your reply!

Maybe I need to be more verbose about my scenario... What
I really want to do is persist some metadata in a
spreadsheet. I'm making an add-on which allows getting
data from another application. The user goes on a menu and
makes some settings (selection criteria, layout and so on)
in some user forms. The add-on then fetches the data and
inserts that as well as some formulas into the
spreadsheet. Now, I want to persist the settings directly
in the spreadsheet so a) they can be reviewed/changed
without the need to start over, and b) the data can be
refreshed at any time.

The data belongs to the user and it should therefore be up
to her to decide if she wants to protect a sheet or
workbook.

All I want is to persist my metadata; but to do that I
need to put some representation of it into a cell. I've
chosen xml, and I assign it to the Value (not the formula)
of a cell. I do some boring workarounds to minimize the
annoyance: Set font size 1, set font color to background
color of cell, set format to text content. That works
kinda ok (so long as the user doesn't change any colors),
yet it's showing up in the formula bar is a more serious
annoyance. The formula bar takes a third of my screen when
I navigate to that cell. But a cell's formula (or value)
is not shown in the bar if

a) the FormulaHidden property is True

AND

b) protection is active

So, to rephrase my original question: Can I hide the cell
contents without dictating anything about the protection
of the sheet?

BTW, if you have any other ideas about persisting the
metadata that would be more than welcome. I have made
three posts here about the problem of persisting metadata
and nobody seems to have any opinions or ideas on the
matter.

Have a good day,

Dag

-----Original Message-----
You want to keep the formulas hidden from the user, but
allow them to
protect/unprotect the worksheet whenever they want???

I don't think you can do that using builtin features.

But you could replace your worksheet formulas with
UserDefined Functions
(rewrite them in VBA) and then the user can see your
function and you'll still
have some secrecy:

=myTopsecretformula(a1,b1,c1)

wouldn't mean much to me.

On the other hand, the really interested could buy a
password cracker that will
unprotect your code and then they could see it.

Dag Johansen wrote:

Hi,

is it possible to use protection at the Range level
rather
than the Sheet?

I would like a module to lock a cell and hide it's
formula, but at the same time the end user should be
able
to protect the sheet/book using a password of her own
choice. Can it be done?

Hope for a positive answer! Meanwhile, happy coding.

Dag Johansen

--

Dave Peterson

.

There's no way of hiding the formula without protecting the worksheet (as far as
I know).

I replied to an earlier post:
http://groups.google.com/groups?thre...97AD%40msn.com

But none of them were really very good. I think whatever you do could be
inadvertently broken pretty easily.



Dag Johansen wrote:

Hi Dave,

thanks for your reply!

Maybe I need to be more verbose about my scenario... What
I really want to do is persist some metadata in a
spreadsheet. I'm making an add-on which allows getting
data from another application. The user goes on a menu and
makes some settings (selection criteria, layout and so on)
in some user forms. The add-on then fetches the data and
inserts that as well as some formulas into the
spreadsheet. Now, I want to persist the settings directly
in the spreadsheet so a) they can be reviewed/changed
without the need to start over, and b) the data can be
refreshed at any time.

The data belongs to the user and it should therefore be up
to her to decide if she wants to protect a sheet or
workbook.

All I want is to persist my metadata; but to do that I
need to put some representation of it into a cell. I've
chosen xml, and I assign it to the Value (not the formula)
of a cell. I do some boring workarounds to minimize the
annoyance: Set font size 1, set font color to background
color of cell, set format to text content. That works
kinda ok (so long as the user doesn't change any colors),
yet it's showing up in the formula bar is a more serious
annoyance. The formula bar takes a third of my screen when
I navigate to that cell. But a cell's formula (or value)
is not shown in the bar if

a) the FormulaHidden property is True

AND

b) protection is active

So, to rephrase my original question: Can I hide the cell
contents without dictating anything about the protection
of the sheet?

BTW, if you have any other ideas about persisting the
metadata that would be more than welcome. I have made
three posts here about the problem of persisting metadata
and nobody seems to have any opinions or ideas on the
matter.

Have a good day,

Dag

-----Original Message-----
You want to keep the formulas hidden from the user, but
allow them to
protect/unprotect the worksheet whenever they want???

I don't think you can do that using builtin features.

But you could replace your worksheet formulas with
UserDefined Functions
(rewrite them in VBA) and then the user can see your
function and you'll still
have some secrecy:

=myTopsecretformula(a1,b1,c1)

wouldn't mean much to me.

On the other hand, the really interested could buy a
password cracker that will
unprotect your code and then they could see it.

Dag Johansen wrote:

Hi,

is it possible to use protection at the Range level
rather
than the Sheet?

I would like a module to lock a cell and hide it's
formula, but at the same time the end user should be
able
to protect the sheet/book using a password of her own
choice. Can it be done?

Hope for a positive answer! Meanwhile, happy coding.

Dag Johansen

--

Dave Peterson

.


--

Dave Peterson

Hi Dag,

Possibly you can store what you're currently displaying in
a section of the sheet inaccessible to the user (How-to to
follow) and on any SheetChange event reload a values only
copy of the displayed data for the user. If so, I think
you can find a way to (With Excel 2000 & newer) set the
ScroolArea Property for a defined user area within an
OnSheetActivate routine to keep the user out of the
sensitive area. With earlier versions of Excel, you
might try using the DataEntryMode property, but this is
likely to be too restrictive.

If I understand correctly that your real concern is the
user annoyance at seeing the formulas, it should not
matter that the above can be user subverted.

Best Regards,
Walt

-----Original Message-----
Hi Dave,

thanks for your reply!

Maybe I need to be more verbose about my scenario... What
I really want to do is persist some metadata in a
spreadsheet. I'm making an add-on which allows getting
data from another application. The user goes on a menu
and
makes some settings (selection criteria, layout and so
on)
in some user forms. The add-on then fetches the data and
inserts that as well as some formulas into the
spreadsheet. Now, I want to persist the settings directly
in the spreadsheet so a) they can be reviewed/changed
without the need to start over, and b) the data can be
refreshed at any time.

The data belongs to the user and it should therefore be
up
to her to decide if she wants to protect a sheet or
workbook.

All I want is to persist my metadata; but to do that I
need to put some representation of it into a cell. I've
chosen xml, and I assign it to the Value (not the
formula)
of a cell. I do some boring workarounds to minimize the
annoyance: Set font size 1, set font color to background
color of cell, set format to text content. That works
kinda ok (so long as the user doesn't change any colors),
yet it's showing up in the formula bar is a more serious
annoyance. The formula bar takes a third of my screen
when
I navigate to that cell. But a cell's formula (or value)
is not shown in the bar if

a) the FormulaHidden property is True

AND

b) protection is active

So, to rephrase my original question: Can I hide the cell
contents without dictating anything about the protection
of the sheet?

BTW, if you have any other ideas about persisting the
metadata that would be more than welcome. I have made
three posts here about the problem of persisting metadata
and nobody seems to have any opinions or ideas on the
matter.

Have a good day,

Dag

-----Original Message-----
You want to keep the formulas hidden from the user, but
allow them to
protect/unprotect the worksheet whenever they want???

I don't think you can do that using builtin features.

But you could replace your worksheet formulas with
UserDefined Functions
(rewrite them in VBA) and then the user can see your
function and you'll still
have some secrecy:

=myTopsecretformula(a1,b1,c1)

wouldn't mean much to me.

On the other hand, the really interested could buy a
password cracker that will
unprotect your code and then they could see it.

Dag Johansen wrote:

Hi,

is it possible to use protection at the Range level
rather
than the Sheet?

I would like a module to lock a cell and hide it's
formula, but at the same time the end user should be
able
to protect the sheet/book using a password of her own
choice. Can it be done?

Hope for a positive answer! Meanwhile, happy coding.

Dag Johansen

--

Dave Peterson

.

.