Not allowing locked cells to be referred to in formulae

[Gemma S]

Hi

I used to be a regular user of this board around 1999/2000 but my career then took me away from VBA etc and now I have a piece of work I want to do and I am very rusty (understatement)

What I am trying to achieve is for a spreadsheet to include salary information that can only be seen by one user with a password, the rest of the workbook is available to others

I have a simple macro that locks those cells, does not allow them to be selected and turns the font while so it wont show. I then have a input box to enter a password to be able to run the macro.

My problem is that a savvy user could write a formula that will reveal the data (ie if A1 - A10 has salary info, you can type =A3 into the formula bar and see the data)

Is there a way of stopping a user being able to reference cells that are locked?

I am trying to avoid have the salary data in another file or hidden sheet for clarity. Is there a way?

Thanks

Gemma

 

[jasonb75]

There are still going to be ways around it if the user is determined to find the hidden information. This looks like it works but I've only done a very quick test.
Using the example code, if you try to add a formula that refers to any cell in A1:A10 or C1:C10 then this will instantly delete that formula. Should be easy to modify as required.
I've done it with the ranges defined in the code, but it could be done by looping through each cell in the precedent range to see if any are locked.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target.Precedents, Range("A1:A10,C1:C10")) Is Nothing Then Target.ClearContents
End Sub