Not allowing locked cells to be referred to in formulae

Gemma S

New Member
Joined
Sep 9, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,998
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,640
Members
410,696
Latest member
JTrehan
Top