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

By : Dheer
Source: Stackoverflow.com
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.

By : Dheer


Answers

Try using

Worksheet.Protect "Password", UserInterfaceOnly := True

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

By : Joe


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.



This video can help you solving your question :)
By: admin