I am trying to lock a few cells so that users can not change the formula in the cell but I want the formula in the cell to work. I have found that I protect the worksheet the formulas will not work or is will not allow me to export in the information.
uTab
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi uTab,
I am not sure what you want...
1) Select the entire sheet (Ctrl + A)...
2) Format --> Cells --> Protection (Tab).
3) Uncheck Locked
4) Click OK
Now..
1) Select only the cells which contain the formulas.
2) Format --> Cells --> Protection
3) Check Locked
4) Click OK
Then...
1) Tools --> Protection --> Protect Sheet --> OK
Bye
---
Harish
uTab,
It is also possible to "protect" the formulas in certain cells by using the Worksheet_Change event sub to trap attempts to change them. The benefit of this approach is that you avoid certain complexities that otherwise occur when a worksheet is protected. The required code must go in the code pane for the worksheet being watched.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Union([A1:A1], [B3:B4], [C5:C5]) 'List as many cells to be "protected" as you like
If Intersect(Target, rg) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You are not permitted to change the formula in " & rg.Address
End Sub
To install a sub in the code pane for a worksheet:
1) Right-click the sheet tab for the worksheet
2) Choose View Code from the resulting pop-up
3) Paste the suggested code in the resulting module sheet
4) ALT + F11 to return to the worksheet
Brad
uTab,
When you open the workbook, unprotect the worksheet and then apply protection with UserInterfaceOnly=True. Your macros may then change protected cells.
Sub Workbook_Open()
Worksheets("Sheet1").Unpro
Worksheets("Sheet1").Prote
End Sub
To install the sub in the code pane for ThisWorkbook:
1) ALT + F11 to open the VBA Editor
2) If you don't see a list of VBA projects on the left, then CTRL + R to open the Project Explorer
3) In the Project Explorer window, double-click ThisWorkbook to open its code pane
4) Paste the suggested code in the resulting module sheet
5) ALT + F11 to return to the spreadsheet
Brad
Business Accounts
Answer for Membership
by: byundtPosted on 2005-05-08 at 08:47:32ID: 13954560
Hi uTab,
Before protecting a worksheet, you should unlock all the cells you want the user to be able to change. To do this:
1) Select the cells the user will be permitted to change. It may be convenient to hold the Control key down while you do this
2) Open the Format...Cells...Protectio
If there are only a few cells you want to keep locked, it will be more convenient to unlock all of them by using the Edit...Select all menu item in step 1. You can then repeat steps 1 and 2, only checking the "Locked" checkbox for those cells you want to prevent the user from changing.
Protecting the worksheet will not prevent formulas from working, but (incorrectly done) it may prevent the user from making the desired changes.
Cheers!
Brad