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.
How to protect cells in Excel but allow these to be modified by VBA script
Worksheet.Protect "Password", UserInterfaceOnly := True
If the UserInterfaceOnly parameter is set to true, VBA code can modify protected cells.
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:
and re-protect it after you're done:
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.