Safe Guarding Excel Worksheet - Cells

HI all,

I need to be able to protect a formula in a cell.

However there are a few complication we have a third party plugin that is linked to our accounting program which allows us to drill down and pull through information on customer accounts. If i lock the cell so it cant be changed then the drill down function does not work. But if i leave it unlocked its easy for people to accidently delete and mess up the formula and the functions set up in excel.

Any thought or suggestions round this problem greatfull received.

You can do this with a very, very simple macro.

For ease, close any other Excel workbooks you have open.

To do this, write the formula in the cell (assume you have already done this). Then, select any other cell.

Turn on the macro recorder (Tools >> Macro >> Record New Macro). Note the name (probably Macro1; change this if you like).

Click back in the cell with the formula. Press F2. Press Enter. Turn off the Macro Recorder (Tools >> Macro >> Stop Recording).

Open the VB Editor (easiest way: press Alt+F11).

You should see a window on the Left side that says at the top something like Project - VBAProject. This is the Project Explorer window. If you do not see this, use the Menu bar and click View >> Project Explorer.

Under Microsoft Excel Object, find the sheet that you have the formula on. Double-click on that to see its code window. At the upper left of the code window (should be on the right side of the VB Editor screen), select Worksheet. On the upper right of the code window, select Calculate.

This should create an empty subroutine named "Private Sub Worksheet_Calculate()". Type in the following:

Please Login or Register  to view this content.

That's it.