How do I avoid run-time error when a worksheet is protected in MS-Excel?

Question

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

Me.unprotect
...
Me.protect

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?

Update

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"
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 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

    ...

    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: 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!

Answer 2

I'm not sure if this is a universal solution, but when I've had this error recently, I just had to do a MywkSheet.Activate right before I did the 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 was off already, so the user never sees the sheets switching back and forth. HTH.