Protecting 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

User · Answer

A basic but simple to understand answer   Sub Example       ActiveSheet Unprotect     Program logic        ActiveSheet Protect End Sub

User · Answer

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

User · Answer

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

User · Answer

I selected the cells I wanted locked out in sheet1 and place the suggested code in the open workbook   function and worked like a charm   ThisWorkbook Worksheets  Sheet1   Protect Password   Password     UserInterfaceOnly  True

User · Answer

Try using   Worksheet Protect  Password   UserInterfaceOnly    True   If the UserInterfaceOnly parameter is set to true  VBA code can modify protected cells

User · Answer

As a workaround  you can create a hidden worksheet  which would hold the changed value  The cell on the visible  protected worksheet should display the value from the hidden worksheet using a simple formula    You will be able to change the displayed value through the hidden worksheet  while your users won t be able to edit it

[vba] Protecting cells in Excel but allow these to be modified by VBA script

Examples related to vba

Examples related to excel