Does it error for you in that case? All cells are Locked by default. I am aware of setting protection to certain cells by the Excel Options. If this argument is omitted, protection applies both to macros and to the user interface. For Each rng In ActiveSheet. Then post a link to the uploaded and shared file here. Excel ® is a registered trademark of the Microsoft Corporation.
Also, I will need to protect 12 non adjacent ranges. Dear Crystal, Is there anyway that this macro can be run automatically upon opening the file, or upon clicking any cell. For example: I11:I20 and K11:K20 and M11:20 etc. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window. Is this how you wanted the macro to work? Users entering data into the wrong cells or changing existing formulas can make data collection a tedious process. The default value is False. Right click the sheet tab, and select View Code from the right-clicking menu.
Does that make a little more sense? It'll make future maintenance easier. A simple loop can be used to detect cells in a given range that contain formula. Edited code:- Your comment is not clear, nonethless I believe you want to use a macro. For a chart, this protects the entire chart. Kind regards, Robbert Use ActiveSheet.
When I ran the Protect routine, the entire worksheet got protected, not just the cells in the Range command. All cells that are Locked are fully protected when Sheet protection is applied and includes Contents. Since these and other functionalities are needed for the end-user, I cannot protect the workbook. That's a great idea on making a password array, I like that very much. We do not want these users to be allowed to enter data into, or change any information in the range A12:R61 though. Also a user can enter a particular password the first time and then a different password each time after that and it will be accepted. Unprotect ' Code to manipulate cells goes here.
The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. So I think you're onto something, but your diagnosis isn't quite right. If I am correct this is not an extra setting for the Protect function, so what should I do to make this functionality available for the end-user? Tom Thanks for your help, Dave, but it didn't work. It's inelegant, but gets the job done. Yes I want to prevent others to change de value of the cells. The user's name is determined at a password prompt and then entered in to cell K18. I have created an add-in that sends and retrieves data from a database in order for this data to be used by our analysts.
Is it entered manually by the user or is it copied and pasted or is it done by a formula? It returns a Boolean value based on whether the cell or range has a formula. Copy and paste the code into the module to run it. I'll happily buy you a nice piece of Beemster if we ever meet! True to protect the user interface, but not macros. These cells will always remain unlocked for data entry. Still, I want to lock the cell containing the data points. Lock Cells Important note: Protecting a sheet does not lock individual cells! I also want to prevent users from clobbering those other cells, so those are Locked. I'm afraid I cannot explain the problems you're experiencing.
If you try to select cell A3 or A5 in current worksheet, the cursor will be moved to the right adjacent cell automatically. Would this work for you? When these users do this, they put demographic information into some cells in the range A1:I11. If so I suggest you having a code in vba. They are: DrawingObjects: Optional Variant. Thank you for your comment.
But the function works fine for me if the worksheet is unprotected. The cells and ranges have a HasFormula property, which makes them read only. In other words, protection would be applied to I11:I20 and K11:K20 and M11:20, but a user would be able to add a new row row 21 , with new data, but once the new row is added, then the protection would apply to I11:I21 and K11:K21 and M11:21. Thanks so much for the help! As of now the sheet1 has protection property set through the toolbar properties. Sub ProtectCellsWithFormulas For Each rng In ActiveSheet. I have used the code in Carlos' comment to run the macro automatically as you open the file.