Hiding my formulas

[silks]

Hiya all,

How can I prevent users seeing the formula behind a cell if they clic
on it? Or alternatively how I can lock a cell so that they can't clic
on it - i.e. it jumps to the next unlocked cell?

Thanks

 

[Frank Kabel]

Hi
first format your cells accrodingly: goto 'Format - Cells - Protection'
and check 'Locked' and/or 'hidden'
After this protect your worksheet: 'tools - Protection

 

[J. Doe]

In Excel 2002 sp2 you could achieve this by protecting your worksheets.
Select the sells that you ore your users are allowed to change. Under
<Format> <Cell> go to the protection tab and mark the selected sells as
unprotected.

Using <Tools> <Protection> <Protect sheet> to protect the workbook. As soon
as this is selected you would be given the option to allow selection of
unlocked cells only. Doing this will only allow the user to select the
cells you have access the cells you have unprotected.

Hope this helps
(PS You would have to give your sheet a password protection otherwise your
user can view your formulas by unprotecting the sheet)

 

[LABMS]

There are a couple of options open to you.
The most straightforward one is to protect your worksheet.
First, select the cells which the user will have to
interact with. You must 'unlock' these cells:

Select cell, Right Click/Format Cells/Protection (Far
Right Tab)/ Untick top box.

I would then select all cells (Use Ctrl & A, or just click
on the grey square TopLHS, between column A and row 1)and
then 'hide' the contents of the cells:

Select cell, Right Click/Format Cells/Protection (Far
Right Tab)/ Tick bottom box.

Make sure the box is not shadowed - keep clicking until it
is full on black. (Shadowed means some cells are currently
hidden, others are not).

When you are happy with the sheet, protect it:
For Excel 2000 & prior:
Tools/Protection/Project Sheet/ Leave the boxes ticked.

In Excel XP -> onwards the dialogue box is different. The
ticked options are completely different: here I would
leave "Select unlocked cells" ticked, but untick "Select
locked cells." This stops users selecting any but
your "interaction" cells, and they can use the "Tab"
button quickly to navigate from one of your interaction
cells to the next. SHIFT+Tab takes them backwards. In pre-
XP versions, this happens automatically when the sheet is
protected.

You may password protect the protection if you want. If
you don't, savvy users may remove the protection to
tinker; most users will just leave it alone anyway.

Now, with protection on, the user can tab between the few
cells he or she needs to change; the cell can be changed
because it is "unlocked," but the underlying formulae in
locked and hidden cells cannot be seen.

If your users find it a pain having the interaction
cells "hidden," because it stops them editing the cell (by
this I mean they CAN change the contents of the cell, but
they must do it from scratch: options such as "F2 to
edit," or clicking on the cell to change the existing
entry may be lost) then you can select your interaction
cells and run through the 'hide' routine above again, this
time of course unticking the relevant box.

Alternatively, you may decide that it's quicker having all
your calculations done on a separate sheet, (or even in a
separate workbook, but then you have to make sure that you
always keep them together), which means that
your "results" cells all refer to cells in that sheet: you
can then hide the calculation sheet:

Format/Sheet/Hide

and then Protect the whole workbook:

Tools/Protection/Protect Workbook

This will stop the little critters unhiding your
calculation worksheet. This can again be password
protected, to err on the safe side.

Hope this helps

LABMS

 

[dhart]

On a similar note, we've inherited a spreadsheet with locked cells. We
need to enter formulas in the cells that are not locked, then return
the file to its creator with just values. I was thinking of writing a
macro to select only the non-locked cells, then triggering a
Copy>PasteSpecial>Values. When I test how this would work from the
menu or F5 function key, the Edit>GoTo shows "SPECIAL" as unavailable
(grayed-out). Any way around this?