title: How do I avoid run-time error when a worksheet is protected in MS-Excel?
body: <p>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</p>
  
  <blockquote>
    <p>Run-time error '-2147417848 (80010108)':</p>
    
    <p>Method 'Add' of object 'Validation' failed</p>
  </blockquote>
  
  <p>I have tried wrapping the code with </p>
  
  <pre><code>Me.unprotect
  ...
  Me.protect
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>Update</strong></p>
  
  <p>My original work book is an Excel 2003. I tested @<a href="https://stackoverflow.com/questions/445519/how-do-i-avoid-run-time-error-when-a-worksheet-is-protected-in-ms-excel#447247">eJames</a> solution in Excel 2007 with the following definition for Workbook_Open</p>
  
  <pre><code>Sub WorkBook_Open()
      Me.Worksheets("MainTable").Protect  contents:=True, userinterfaceonly:=True 
  End Sub
  </code></pre>
  
  <p>The code still fails with the following run-time error when the worksheet is protected</p>
  
  <blockquote>
    <p>Run-time error '1004':
    Application-defined or object-defined error</p>
  </blockquote>
  
  <p>Thanks, Azim</p>
  
  <hr>
  
  <p><strong>Code Snippet</strong></p>
  
  <pre><code>'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:="=" &amp; myNamedRange
  
     ... 
     ' skipping more property setting code '
     ...
  End With
  </code></pre>