Hiding Formulas

May 30, 2009 at 08:19:55
Specs: Windows XP, 1.0 Gb
Is there a way to hide formulas so they are not visible when their respective cells are clicked?

Thank you.

See More: Hiding Formulas

Report •

Report •

May 30, 2009 at 13:33:55
One drawback of the method described in the options that Mike offered is that you have to manually unlock any cells that you want the users to be able to enter data in.

There is a VBA workaround that hides the formula and locks only the cells that contain a formula. That method can be found here:


Report •

June 1, 2009 at 04:01:33
Mike and DerbyDad03,

Thank you for your suggestions. As there are many formulas, I would like to try the VBA solution; however, I don' know how to do that. How do you enter VBA script?

Thank again,
Brian W

Report •

Related Solutions

June 1, 2009 at 06:54:43
As per the instructions given at the link I suggested...

Open the VBE (Alt+F11) then double click ThisWorkbook to access the private module of the Workbook Object.

(You can also open the VBE by right-clicking a sheet tab and choosing View Code. Then double click ThisWorkbook.)

Paste the code in the ThisWorkbook pane and you're done - sort of.

One thing that is not discussed at the site, is how you, the author of the workbook will edit your formulas since they are hidden and locked.

First, you will need to unprotect the workbook using the password that is given in the code. (You can change this password by replacing "Secret" with whatever you want.)

Then you need to temporarily stop the macro from running whenever you select a cell and edit it. You can do this by putting an apostrophe before the word Private. Once you've made your changes, remove the apostrophe and the code will run - and protect the worksheet - the next time you select any cell.

Finally, if you don't want your users to view - and possibly edit - the code (which contains the password) you'll need to hide and protect the VBA module itself.

Right-click the ThisWorkbook module and choose VBAProject Properties, Protection tab.

Report •

June 13, 2009 at 07:24:18
Thank you for the information. These procedures are a little too involved for me so I'm going to keep the formulae as is.

Report •

June 13, 2009 at 15:26:59
I guess it's a trade off between having to fix the worksheet - or worse yet, get inaccurate results - if your users screw with the formula and following a procedure that appears to be "involved" but is really quite simple.

You might also find that if you try the procedure, you'll expand your Excel experience and learn some useful things about VBA.

Report •

June 23, 2009 at 04:50:23
Dear DerbyDad03 and Mike,

As the project I have been working on for several months is now completed, I want to thank both of you for your assistance and your willingness to share your expertise in Excel.

Brian W.

Report •

June 23, 2009 at 06:21:43
Glad to have been of some assistance.

Report •

June 23, 2009 at 07:47:33
Me too.



Report •

Ask Question