Alternative to locking cells

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
573
Office Version
  1. 365
Platform
  1. Windows
Is there a way to prevent users from entering information into cells that have formulas short of protecting the cells.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You could use a Worksheet_SelectionChange event macro to literally PUSH the cursor out the columns that have formulas you don't want touched. This would have the added benefit of moving their cursor to the cell you DO allow user interaction. Like this would keep the cursor out columns C, D and E and push the cursor over to F.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
        Range("F" & Target.Row).Select
    End If
End Sub
 
Upvote 0
I will give this a try. One question will this allow for the user to insert and delete rows or to format cells. I would like them to have this ability but when I protect the whole column it takes this ability away from them.
 
Upvote 0
I will give this a try. One question will this allow for the user to insert and delete rows or to format cells. I would like them to have this ability but when I protect the whole column it takes this ability away from them.

Yes, as long as they can get into the cell, the can interact with it normally.
 
Upvote 0
I think hes saying can they access those cells through the column headers. I am no expert but try instead of C:E try the actual range in other words C1:E10 or of course whatever your range is.
 
Upvote 0
Why do you eschew the idea of using protection to protect your cells? Yes, you can also use VBA to prevent users from altering values of cells. Either by preventing selection as shown here or by forcing an undo in the _Change event handler. However the use of code then relies on the user enabling macros which involves the old splash screen and more coding. Not to mention that if their security is medium or better you now need to learn about digital signatures to avoid those annoying popups on opening (unless using Excel 2007’s Trusted Locations). Is all of this really a better option than just turning on protection?

If so, a tip -- tie to your code to named ranges. If you code to range addresses, your code will begin to fail as soon as cells above or left of your "protected" cells is inserted or deleted.
 
Upvote 0
I had to look up the word eschew in my unabridged dictionary so I could get an educated handle on this thread.

Is there a way to prevent users from entering information into cells that have formulas short of protecting the cells.
Now take a close look at what you are asking. You are asking for formulas to not be overriden with unwanted constant entries, or even other formulas.

You did not say you want to guard against drag and drop, or copy and paste, or fill with the fill handle, or hitting the Delete key, or whatever other way there is to obliterate formulas in cells when you are, yes I'll say it, eschewing the native Lock and Protection scheme.

Instead of VBA, the demerits of which have been heretofore delineated, you have one other option, that being data validation.

From any cell on your worksheet press Ctrl+A+A.

From the worksheet menu, click Edit > GoTo > Special and then select Formulas and click OK.

From the worksheet menu, click Data > Validation. In the Allow box select Custom.

In the Formula field enter
=""
That is, type in the equals sign and two double-quote characters.

Set whatever other options you want to on the other 2 DV dialog tabs, and click OK.

Click any cell to deselect all formula-containing cells.

Now when you attempt to enter anything in a formula-containing cell, you will be disallowed by Data Validation.

As I said earlier, there are still a myriad of ways that the formulas are still exposed to for obliteration, but if you have your heart set on no Lock and Protection, so go the risks of eventual disappointing outcome.
 
Upvote 0
Nifty idea, Tom. I would not have thought about applying validation in this context.

I don't know if it came across clearly - Tom is not saying that validation is robust against drag & drop, copy & paste and so forth. He's saying that protection *is*. The validation-based solution Tom describes does not prevent such user actions. It only protects against users typing over the formulae in the cells
 
Upvote 0
I prefer to have the cells locked, the users of the workbook found this to be a constrant when they would need to for example insert a row. Since whole columns are protected the only way for a new row to be inserted is for me to unlock the worksheet insert the row for them and then lock the worksheet. I was just wondering if there was a way to allow them to insert new rows when needed and to be able to copy the row above into the new row.
 
Upvote 0
What version of Excel are you using? At least in 2003, at the time of setting protection, you would simply check the boxes to allow the users to insert rows &/or columns. I can't remember in which version of Excel these protection options became available, so I cannot speak to Excel 2002 or lower.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top