How to protect cells in Excel but allow them to be modified using VBA script

Question

I use Excel, where certain fields are allowed for user input, and other cells must be protected. I used the Tools Protect sheet, but after that I cannot change the values ​​in the VBA script. I need to limit the worksheet to stop user input, at the same time allow VBA code to change cell values ​​based on certain calculations.

Answer 1

Try using

Worksheet.Protect "Password", UserInterfaceOnly := True 

If UserInterfaceOnly is set to true, VBA code can modify protected cells.

Answer 2

You can change the sheet through the code by following these steps.

• Unprotect
• Edit
• Protection

In the code, it will be:

 Sub UnProtect_Modify_Protect() ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="Password" 'Unprotect ThisWorkbook.ActiveSheet.Range("A1").FormulaR1C1 = "Changed" 'Modify ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password" 'Protect End Sub 

The weakness of this method is that if the code is interrupted and error handling does not capture it, the worksheet can be left in an unprotected state.

The code can be improved by following these steps.

• Re-protection
• Edit

Code for this:

 Sub Re-Protect_Modify() ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password", _ UserInterfaceOnly:=True 'Protect, even if already protected ThisWorkbook.ActiveSheet.Range("A1").FormulaR1C1 = "Changed" 'Modify End Sub 

This code updates the protection on the worksheet, but with the UserInterfaceOnly parameter set to true. This allows the VBA code to modify the worksheet, keeping the personal sheet protected from user input through the user interface, even if execution is interrupted.

This parameter is lost when the workbook is closed and reopened. Worksheet protection is retained.

Therefore, the Re-Protection code should be included at the beginning of any procedure that tries to change the worksheet or can only be run once when the workbook is opened.

Answer 3

I don’t think that you can set any part of the sheet to edit only VBA , but you can do something that has basically the same effect - you can unprotect the sheet in VBA before you need to make changes:

 wksht.Unprotect() 

and re-protect it after you are done:

 wksht.Protect() 

Edit: It looks like this workaround might have solved the immediate Dheer problem, but for those who come up with this question / answer later, I made a mistake in the first part of my answer, as Joe points out. You can protect a worksheet that needs to be edited only by VBA, but it seems that the option "UserInterfaceOnly" can be set only by calling "Worksheet.Protect" in the code.

Answer 4

The main, but easy to understand answer:

 Sub Example() ActiveSheet.Unprotect Program logic... ActiveSheet.Protect End Sub 

Answer 5

As a workaround, you can create a hidden worksheet that saves the changed value. The cell on the visible, protected sheet should display the value from the hidden sheet using a simple formula.

You can change the displayed value using a hidden worksheet , while your users will not be able to edit it.

Answer 6

I selected the cells that I wanted to block in sheet 1, and put the proposed code in the open_workbook () function and worked like a charm.

 ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password", _ UserInterfaceOnly:=True