Excel – How to avoid run-time error when a worksheet is protected in MS-Excel


The code snippet below changes the data validation state of a cell and runs when the Excel-2003 worksheet is unprotected. However, when I protect the work sheet the macro doesn't run and raises a run-time error

Run-time error '-2147417848 (80010108)':

Method 'Add' of object 'Validation' failed

I have tried wrapping the code with


But this does not work properly. So, how can I modify the code below to work (ie have the code modify the unlocked cell's validation) when the sheet is protected without the above run-time error?


My original work book is an Excel 2003. I tested @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 fails with the following run-time error when the worksheet is protected

Run-time error '1004':
Application-defined or object-defined 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"
   myNamedRange = "range2"
End If

With modifyCell.Validation

   'Run time error occurs on the next line'
   .Add Type:=xlValidateList, AlertStyle:=xlValidAltertStop, _
        Operator:=xlBetween, Formula1:="=" & myNamedRange

   ' skipping more property setting code '
End With

Best Solution

If I understand the question correctly, you will be the one protecting the sheet. If that is the case, you can use the following VBA:

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

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

Place this code into the WorkBook_Activate event to automatically protect the workbook and set the flag whenever it is activated.

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

Edit: Since the above didn't seem to work, you may have to wrap the failing portion of your VBA code with unprotect/protect commands. If you do that, I would also wrap the entire macro with an error handler, so that the sheet is not left unprotected after an error:

Sub MyMacro
    On Error Goto HandleError


    With ModifyCell.Validation
    End With
    myWorksheet.protect contents:=True, userinterfaceonly:=True


Goto SkipErrorHandler
    myWorksheet.protect contents:=True, userinterfaceonly:=True
    ... some code to present the error message to the user
End Sub

Edit: Have a look at this thread at PCreview. They went through much the same steps, and came to the same conclusion. At least you're not alone!

Related Question