I was wondering how you can protect a formula but still allow editing in the cell. Right now I have certain columns locked while allowing others to be edited. I have a formula in one column that needs to be edited if need be but if they make a mistake and hit delete then my formula disappears and it throws the whole sheet off. Is there a way to protect your formula maybe by putting it in a different cell that can be locked and referencing the cell where they can input?
Thanks
Last edited by hbasten; 05-30-2009 at 07:11 PM. Reason: Issue not resolved
U can do it by simple protection.
1. Press Ctrl+A
2. Ctrl+1 > Preotection and uncheck "Locked"
3. Press F5 > special > selct formulas (this will select only cells containing formulas) and OK
4. Ctrl+1 again > protection > check "Locked" (if u wanna hide your formulas check "Hidden" too).
5. Tools>Protection>Protect Sheet. Type pass. click OK.
Other way, if don't want to do that using protection.
1. Select formulas by above-mentioned way (F5...).
2. Data> Validation>Custom
3. Type formula A1=""
This won't allow to edit formulas even if worksheet are unprotected.
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
I forgot to say that Data>Validation method allows to delete formula by hittin' DEL button
Re: protect formula while still allowing editing
Thank you. I know how to protect and lock. However I have one column that allows the user to input in the colum cells and the cells have a formula. If the user input incorrect info they need to be abe to hit delete without deleting the formula. If I put the formula in another cell that I could lock and referenced it to the cells that can be used. Would that work and if so, how would I do that?
hbasten,
a user should not need to edit a formula. Good spreadsheet design keeps data entry and calculation separate, for exactly the issues that you are facing. Why do they need to edit the formula? Can you post an example?
Re: protect formula while still allowing editing
they would not be editing the formula, just inputting in the cell. I have attached a sample. I have unlocked everything so you can see it. When I protect it the only column a user can change is column B where they input a beginning balance. However if the wrong key is hit and they need to delete and re-enter if deletes the formula. I want the user to be able to input in column B but don't want the formula lost. Is there a way to do this?
forgot to say, they can also edit column C, G and H
Look, you either have an input cell, where the user enters a value, or you have a calculated cell. You can't have both at the same time. If column B is calculated from column I, then it should not be editable. If column B needs to be entered by the user, then it should not have a formula in it.
The way your table is laid out, the user will enter data in columns C (%), G (Addition) and H (Withdraw). From that, all other columns are calculated.
In which situation do you see the need for them to change the value in column B??
they also need to enter a beginning balance such as the $5000.00 or whatever the bginning balance is and if they make a mistake on the numbers and hit delete then the formula in that cell is lost. There has to be a way to do this. May use cells in column A for the formula in column B then lock column A. Do I make sense or not?
I'm still looking for some help on this.Originally Posted by hbasten
they also need to enter a beginning balance such as the $5000.00 or whatever the bginning balance is and if they make a mistake on the numbers and hit delete then the formula in that cell is lost. There has to be a way to do this. May use cells in column A for the formula in column B then lock column A. Do I make sense or not?
Which part of contaminated's suggestion you don't understand?I'm still looking for some help on this.
That was in 2009 ...
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
even i am facing the same problem, not getting any sort of help on it!!!!!!!!
is there anyone who can tell me the solution for the same.
i m using Excel 2007, and my problem is :
1) I want user to input his data but don't want to allow him to delete formula in that cell (by pressing DEL).
2) how to set a default value (for Exp. 12%, 1000, 12year,) now the user can change these values according to his requirement, but we can't allow him to change 12Year (FIX).
anyone can help, my mail id is harshsolanki59@gmail.com
Thanks,
Harsh Solanki
Harsh,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
Also, it will be good if you remove your email id from the posts, to save yourself from some spam attacks.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I may get in trouble for posting in another old thread.
It would be nice if Excel inherently did a better job of allowing us to lock individual cells. Sometimes I am stymied by an obvious feature that is still not included, even though there are a lot of incredible features (as well as more than a little bloat).
I may explore this more, because it's useful to me and it comes up a lot.
However, I just experimented with a variation on an idea from ExtendOffice (the wonderful Kutools people). They have some VBA code to prevent specific cells from being deleted.
I think the alert box, etc. is a bit much. And the go-to reminds me of Applesoft BASIC from the 1980's.
Although this is hardly elegant, the following seems to prevent the user from changing anything in Column C:
Add that to the VBA code for the Worksheet.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub
Setting EnableEvents to false is necessary, because otherwise the code will change the cell and you'll wind up in an infinite loop.
Or, to operate on a range:
Hey, it may not be perfect. But it's Not Nothing. Ha ha ha.If Not Intersect(Target, Range("A1:E7")) Is Nothing Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If
I only played with this just now, so I haven't tested it very much. If you are leery, then probably the exact code used by ExtendOffice is a better bet, since they are pretty careful. (You can always take out the alert box or check a column instead of a range, however. EDIT: I am adding one line to this post in a quick edit, to add that that the original code also allows you to enter a date, so if you don't want the user to be able to change anything, then make sure to take out the If/Then that refers to IsDate.)
Also, the previous suggestion by contaminated to use Data Validation is a good one. I've done that before.
You can use Custom and simply enter the formula of 0 (rather than A1=""). However, as he said, that technique, which is very much a hack, still allows the user to use the Delete key to delete the contents of a cell.
You could probably combine that technique with trapping the delete key to create a more sophisticated, and even hackier solution.
You can also get the user-selected range. So you might be able to prevent the user from hitting Delete only in certain cells.
The key to the first solution I mentioned is Worksheet_Change.
Another example of this, from Microsoft, is to set the color of a changed cell --
I'm sure that might come in handy. I've not used that before. I have used Worksheet_BeforeDoubleClick, which works similarly.Private Sub Worksheet_Change(ByVal Target as Range) Target.Font.ColorIndex = 5 End Sub
Anyway, yes, yes, I know, I am an utterly terrible person for adding a post to a thread that was started while the financial crisis was just ending, the iPhone was only in its second generation, and Miley Cyrus was still known as Hannah Montana. I understand my actions, and I am aware that I deserve one of the most excruciating and diabolical punishments ever contrived by a mad man.
But if I am allowed to speak on my own behalf, and in favor of some leniency, if I have listened to Miley Cyrus, then have I not already suffered enough?
Dan
Last edited by sdanex; 07-27-2017 at 09:38 PM. Reason: one minor addition, in context
Who says Excel do not allow you to lock individual cells?
Last edited by AliGW; 07-28-2017 at 02:42 AM.
I don't think anyone did. Dan mentioned that it could do a better job, but did not say that it could not do it.
sdanex, who says Excel does not allow you to lock individual cells?
Joseph - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Yes, I just realised it was a long post.. already edited my post.
Last edited by AliGW; 07-28-2017 at 02:43 AM. Reason: You did it again!
Well, let me clarify why I responded. I have seen this issue come up in various places over time, in various forums.
The question is, how do you prevent the user from changing the contents of certain cells, but not others. You would think that this would be very straightforward, but I have never seen a great solution to this problem.
My solution here, which is derived from the ExtendOffice solution, is not ideal. It uses Application.Undo, which seems like a hack. But incredibly, that may be the best solution that I've happened across. ExtendOffice (the Kutools people) are true Excel geeks, so if using Application.Undo is their best solution, then maybe that is the best one available.
I suggested that there might be a more elegant solution -- or at least a second hack -- if someone is willing to tinker a little bit further.
There were some earlier responses to the question, and contaminated's was good but it still left the problem that a user could change the cell contents by hitting the Delete key (which would erase the formula altogether).
The original question was from May of 2009, but someone asked the same question again in August of 2012.
Last edited by AliGW; 08-14-2017 at 01:53 AM. Reason: Redacted by moderator to bring into line with expected forum etiquette.
>>The question is, how do you prevent the user from changing the contents of certain cells, but not others.
Lock the cells you want to protect. Use the format dialog for that. By default all cells are locked. Unlock the cells that users should be able to edit.
Then protect the sheet with a password. There. Users can only edit unlocked cells.
Thanks, teylyn. That is indeed a great (and simple) solution for a lot of cell locking purposes! And it has the advantage that it is more conventional.
To clarify his answer a little bit, there is more than one way to password "protect" a document. If you go to File --> Info, you want to "Protect Current Sheet" or "Protect Workbook Structure," rather than "Encrypt with Password" (which will require a password from anyone who opens the document). You can also go to the Review tab and click Protect Sheet or Protect Workbook.
There are, however, a few downsides to this solution:
- It locks a cell so that even VBA cannot change it. So you have to unlock a cell if you want to be able to change its value within VBA, and then relock it. But you cannot unlock a cell without first unprotecting the sheet. So it's a little convoluted if you want to use VBA very much.
A search online yields a rather hackish solution to that.
- You have to unlock what you want unlocked, because all cells are locked by default. So if you unlock only the current cells, and then you add some new data, those cells will also be locked by default. You could perhaps make a default template in which all the cells are unlocked by default. Or you could simply make sure to unlock all the cells at the beginning, before then locking what you want to lock.
- As I said, you can't unlock a cell while the worksheet is protected, so to unlock one cell does require you to unprotect the sheet, unlock the cell, and then reprotect the sheet.
- You can't lock cells conditionally.
On that note, the solution I posted was a little bit hacky, and this simpler one is better for man purposes, however the earlier one does allow VBA to change the contents of the cells, and it also allows some extra flexibility for locking cells based on certain conditions. For instance, you could allow the user to change the cell values only and not their formatting. (If your condition is met, then you undo the changes to the cell, and if it's not met, then you don't undo them.)
If you want to permit only say values 1 through 5 in a cell, then Data Validation might be a better way. Although again, that won't prevent the user from deleting the cell contents altogether.
If you do use a password, then of course make sure that you don't forget it, because that would be an added headache. Since it's possible to protect a sheet without using a password, I think that's probably a good solution in many cases, or at least until a final version is saved.
Dan
Right Click on the sheet name at the bottom of excel
Select view code
Paste this code into the module that opens and then close it.
Type 12 into D11 ......... No Problem.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.Row < 9 Or Target.Row > 20 Or Target.Value <> "" Then Exit Sub Application.EnableEvents = False Select Case Target.Column Case 4 Target.FormulaR1C1 = "=SUM(RC[-2]*RC[-1])" Case 5 Target.FormulaR1C1 = "=SUM(RC[-1]*20%)" Case 6 Target.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])" Case 9 Target.FormulaR1C1 = "=SUM(RC[-7]+RC[-3]+RC[-2]-RC[-1])" End Select Application.EnableEvents = True End Sub
Click on Cell D11 delete the entry and press enter. The 12 disappears and your formula reappears.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Can this be used a whole lot of vlookup formulas I have in my invoice sheets?
I pasted the code here but the formula doesn't remain after I delete the entry , not sure what im doing wrong here , im guessing you've made the code specific to the op request.
its a basic command I want , I just need cell= another cell (see yellow highlited cell in images)/enter a number over the formula/delete the number and the formula remains?
as a demonstration im working on cell k5 YELLOW FILL , basically I want all the cells in the columns title NOW to keep the formula after deleted , in this scenario if I delete the number 12 in image 3 then I want the formula =J5 to come back so the number should be 1?
Attachment 629239=
Attachment 629240
Attachment 629241
Attachment 629242
thanks in advance for any help
Last edited by Egosbar; 06-21-2019 at 07:54 PM.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hello. This answer you provided in 2017 is exactly what I have been looking for. Your code with the original posters spreadsheet works well. I have been trying to incorporate this code into my spreadsheet without any luck. I'm stumped. Is it best to start a new thread with you and upload my sheet to request your assistance?Right Click on the sheet name at the bottom of excel
Select view code
Paste this code into the module that opens and then close it.
Type 12 into D11 ......... No Problem.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.Row < 9 Or Target.Row > 20 Or Target.Value <> "" Then Exit Sub Application.EnableEvents = False Select Case Target.Column Case 4 Target.FormulaR1C1 = "=SUM(RC[-2]*RC[-1])" Case 5 Target.FormulaR1C1 = "=SUM(RC[-1]*20%)" Case 6 Target.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])" Case 9 Target.FormulaR1C1 = "=SUM(RC[-7]+RC[-3]+RC[-2]-RC[-1])" End Select Application.EnableEvents = True End Sub
Click on Cell D11 delete the entry and press enter. The 12 disappears and your formula reappears.
Yes, you are actually required to start your own thread - and reference this 1 if you think it is relevant.
Right, well, I like that Worksheet_Change event too. You could use Data Validation to prevent the user from entering various values, and then use
...whatever (for instance, Application.Undo or anything else)...If Target.Value = "" Then
In that case, the only circumstance in which Excel would undo what the user just did is if the user entered nothing at all in the target cell, which previously contained something else (or in other words, if the user just deleted the contents of the cell).
There isn't one right or wrong approach, but there are some good ideas here. I still think that it could be easier to do a straightforward cell lock in Excel -- maybe just the ability to add a flag to a cell so that Excel says, "Seriously, bro, did you really want to change that? Because I don't think that you really wanted to change that. But if you did really want to change that, then go ahead and change that (after waiving my liability by clicking OK)."
I do think that it's useful to have a few different approaches, and that is one thing that I like about the Excel/VBA combination in general. Excel, or as Microsoft calls it, "the world's most popular functional language."
It must be good if it's scorned by elite programmers the world over for being far too useful to ordinary people. After all, with a tool like Excel, people are able to actually get things done without elite programmers! Which, if you spent a whole bunch of time becoming an elite programmer, is a truly terrible and awful, no good, very bad thing.
Dan
irrelevant comments deleted by admin
Last edited by FDibbins; 08-19-2017 at 02:09 AM.
Why is this thread being used a a chat room
Ben Van Johnson
smsdalleisback,
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
File attached. I would like the address in the purchase order (B11, B12, E12,B13, B14, B15, B16) to be locked but also able overwrite the formula if needed to add a new vendor on not present in the VENDOR LOOKUP.
The answer I found from 2017 to hbaston's question and his spreadsheet is exactly what I want it to do. His formulas were locked but able to be overwritten and have the formula "reappear" if needed.
I tried to modify this code and incorporate into my sheet without luck. I was able to convert my A1 code to R1C1 code but I'm not sure why the code would need too be shown this way.
Thank you in advance for any help.
It was mehmetcik's code I am referring to.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Row < 9 Or Target.Row > 20 Or Target.Value <> "" Then Exit Sub
Application.EnableEvents = False
Select Case Target.Column
Case 4
Target.FormulaR1C1 = "=SUM(RC[-2]*RC[-1])"
Case 5
Target.FormulaR1C1 = "=SUM(RC[-1]*20%)"
Case 6
Target.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])"
Case 9
Target.FormulaR1C1 = "=SUM(RC[-7]+RC[-3]+RC[-2]-RC[-1])"
End Select
Application.EnableEvents = True
End Sub
Apologies for many posts.. This is the example using mehmetcik's code solving hbasten's issue. It is this code and solution I'd like to rewrite for my issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks