Databases
StackOverflow2013
Postsdbo
POHow do I avoid run-time error when a worksheet is protected in MS-Excel?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I avoid run-time error when a worksheet is protected in MS-Excel?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAzim
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I avoid run-time error when a worksheet is protected in MS-Excel?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I avoid run-time error when a worksheet is protected in MS-Excel?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow do I avoid run-time error when a worksheet is protected in MS-Excel?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload