How to avoid a runtime error when a worksheet is protected in MS-Excel?

Question

The following code snippet changes the state of checking cell data and starts when the Excel-2003 worksheet is not protected. However, when I protect the worksheet, the macro does not start and causes a runtime error

Runtime Error '-2147417848 (80010108)':

Failed to execute 'Add' of 'Validation' object

I tried to wrap the code with

Me.unprotect ... Me.protect 

But this does not work properly. So, how can I change the code below to work (i.e. Change the code to check the unlocked cell) when the sheet is protected without the above error at runtime?

Update

My original workbook is Excel 2003. I tested the @ eJames solution in Excel 2007 with the following definition for Workbook_Open

 Sub WorkBook_Open() Me.Worksheets("MainTable").Protect contents:=True, userinterfaceonly:=True End Sub 

The code still does not work with the following runtime error when the worksheet is protected

Runtime Error "1004": User Defined or Object Error

Thanks, Azim

---

Code snippet

 'cell to add drop down validation list' dim myNamedRange as String dim modifyCell as Range modifyCell = ActiveCell.Offset(0,1) ' set list values based on some conditions not defined for brevitity' If myCondition then myNamedRange = "range1" Else myNamedRange = "range2" End If With modifyCell.Validation .Delete 'Run time error occurs on the next line' .Add Type:=xlValidateList, AlertStyle:=xlValidAltertStop, _ Operator:=xlBetween, Formula1:="=" & myNamedRange ... ' skipping more property setting code ' ... End With 

Answer 1

If I understand the question correctly, you will protect the sheet. If so, you can use the following VBA:

 myWorksheet.Protect contents:=True, userinterfaceonly:=True 

The key part here is "userinterfaceonly: = true". When a worksheet is protected by this set of flags, VBA macros are still allowed to make changes.

Put this code in the WorkBook_Activate event to automatically protect the book and set the flag when it is activated.

Edit: Thanks to Lance Roberts for his recommendation to use WorkBook_Activate instead of Workbook_Open .

Edit: Since the above does not work, you may have to wrap the damaged part of the VBA code with the protection / protection commands. If you do this, I would also wrap the entire macro with an error handler so that the sheet does not remain unprotected after the error:

 Sub MyMacro On Error Goto HandleError ... myWorksheet.unprotect With ModifyCell.Validation ... End With myWorksheet.protect contents:=True, userinterfaceonly:=True ... Goto SkipErrorHandler HandleError: myWorksheet.protect contents:=True, userinterfaceonly:=True ... some code to present the error message to the user SkipErrorHandler: End Sub 

Edit: See this thread on the PCreview page. They went through the same steps and came to the same conclusion. At least you are not alone!

Answer 2

I'm not sure if this is a universal solution, but when I had this error recently, I just needed to make MywkSheet.Activate right before I did Validation.Add. So:

 ' set list values based on some conditions not defined for brevitity' If myCondition then myNamedRange = "range1" Else myNamedRange = "range2" End If ''-------------------------------------------------- Sheets("mysheet").Activate ''-------------------------------------------------- With modifyCell.Validation .Delete 'Run time error occurs on the next line' .Add Type:=xlValidateList, AlertStyle:=xlValidAltertStop, _ Operator:=xlBetween, Formula1:="=" & myNamedRange ... ' skipping more property setting code ' ... End With 

in my case, ScreenUpdating is already disabled, so the user never sees the sheets switch back and forth. NTN.