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

The problem is that for VBA to be able to edit protected worksheets, the sheet must be protected with the "UserInterfaceOnly" parameter set to True. However, the UserInterfaceOnly parameter is NOT saved when the workbook is saved, so errors will happen if the workbook is closed and reopened.

Here's one solution:

1. Create a sub that protects all worksheets and sets the UserInterfaceOnly parameter to true. I split it into ProtectSheet and ProtectAll subs:

_

Sub ProtectSheet(SheetName As String)
    Dim pswd As String
    pswd = "pass" ' YOU SHOULD GET THE PASSWORD FROM SOMEWHERE SECURE, NOT HARDCODE IT

    ThisWorkbook.Worksheets(SheetName).Protect Password:=pswd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False

End Sub


Sub ProtectAll()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ProtectSheet (ws.Name)
    Next ws
End Sub

2. In ThisWorkbook, protect all the worksheets when the workbook opens:

Private Sub Workbook_Open()
    Call ProtectAll ' Protect all with UserInterfaceOnly set to true so VBA can edit
End Sub

Answer 2

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!