How to protect cells in Excel but allow these to be modified by VBA script

Question

I am using Excel where certain fields are allowed for user input and other cells are to be protected. I have used Tools Protect sheet, however after doing this I am not able to change the values in the VBA script. I need to restrict the sheet to stop user input, at the same time allow the VBA code to change the cell values based on certain computations.

Answer 1

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

wksht.Unprotect()

and re-protect it after you're done:

wksht.Protect()

Edit: Looks like this workaround may have solved Dheer's immediate problem, but for anyone who comes across this question/answer later, I was wrong about the first part of my answer, as Joe points out below. You can protect a sheet to be editable by VBA-only, but it appears the "UserInterfaceOnly" option can only be set when calling "Worksheet.Protect" in code.

Answer 2

Try using

Worksheet.Protect "Password", UserInterfaceOnly := True

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

Answer 3

You can modify a sheet via code by taking these actions

• Unprotect
• Modify
• Protect

In code this would 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 could be left in an unprotected state.

The code could be improved by taking these actions

• Re-protect
• Modify

The code to do this would be:

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 renews the protection on the worksheet, but with the ‘UserInterfaceOnly’ set to true. This allows VBA code to modify the worksheet, while keeping the worksheet protected from user input via the UI, even if execution is interrupted.

This setting is lost when the workbook is closed and re-opened. The worksheet protection is still maintained.

So the 'Re-protection' code needs to be included at the start of any procedure that attempts to modify the worksheet or can just be run once when the workbook is opened.

Answer 4

See this question for more details:

http://stackoverflow.com/questions/445519/how-do-i-avoid-run-time-error-when-a-worksheet-is-protected-in-ms-excel