Validation.Modify Runtime Error in Protected Worksheet

Discussion in 'Microsoft Excel Programming' started by Kent Klingler, May 13, 2004.

  1. I've developed a fairly simple spreadsheet for the purpose of collecting
    information. In the worksheet are several pull-down lists created using the
    data>validation>list function in Excel. Based on the selection in one of
    pull-down, other pull-down list source ranges will be adjusted.

    Everything works fine when the worksheet is unprotected, but when I protect
    the worksheet I get the following error message:

    Run-time error '1004';
    Application-defined or object-defined error

    The code generating the error is:

    Range("B19").Validation.Modify xlValidateList, xlValidAlertStop, xlBetween,
    "=$L$6:$L$12"

    Cell B19 is a pull-down list created using the tool>validation function and
    being adjusted using the above VBA statement and "=$L$6:$L$12" is the new
    list source lookup range.

    Any help with the problem will be appreciated.

    Thanks in advance.
     
    Kent Klingler, May 13, 2004
    #1
    1. Advertisements

  2. Remove the xlBetween constant.

    Range("B19").Validation.Modify xlValidateList, xlValidAlertStop, ,
    "=$L$6:$L$12"

    worked fine for me

    --
    Regards

    Juan Pablo González

    "Kent Klingler" <> wrote in message
    news:...
    > I've developed a fairly simple spreadsheet for the purpose of collecting
    > information. In the worksheet are several pull-down lists created using

    the
    > data>validation>list function in Excel. Based on the selection in one of
    > pull-down, other pull-down list source ranges will be adjusted.
    >
    > Everything works fine when the worksheet is unprotected, but when I

    protect
    > the worksheet I get the following error message:
    >
    > Run-time error '1004';
    > Application-defined or object-defined error
    >
    > The code generating the error is:
    >
    > Range("B19").Validation.Modify xlValidateList, xlValidAlertStop,

    xlBetween,
    > "=$L$6:$L$12"
    >
    > Cell B19 is a pull-down list created using the tool>validation function

    and
    > being adjusted using the above VBA statement and "=$L$6:$L$12" is the new
    > list source lookup range.
    >
    > Any help with the problem will be appreciated.
    >
    > Thanks in advance.
    >
    >
     
    Juan Pablo González, May 13, 2004
    #2
    1. Advertisements

  3. Thanks, but removing the xlBetween constant didn't work for me, I still get
    the run-time error. I did find a work-around by unprotecting the worksheet
    prior to executing the VBA statement. And then re-protecting the worksheet
    after executing the VBA statement. Not what I would call graceful,
    certainly functional, and unfortunately leaves the worksheet unprotected to
    unwanted data entry for a split-second.

    I'm certainly open to other suggestions, if there are any.

    "Juan Pablo González" <> wrote in message
    news:...
    > Remove the xlBetween constant.
    >
    > Range("B19").Validation.Modify xlValidateList, xlValidAlertStop, ,
    > "=$L$6:$L$12"
    >
    > worked fine for me
    >
    > --
    > Regards
    >
    > Juan Pablo González
    >
    > "Kent Klingler" <> wrote in message
    > news:...
    > > I've developed a fairly simple spreadsheet for the purpose of collecting
    > > information. In the worksheet are several pull-down lists created using

    > the
    > > data>validation>list function in Excel. Based on the selection in one

    of
    > > pull-down, other pull-down list source ranges will be adjusted.
    > >
    > > Everything works fine when the worksheet is unprotected, but when I

    > protect
    > > the worksheet I get the following error message:
    > >
    > > Run-time error '1004';
    > > Application-defined or object-defined error
    > >
    > > The code generating the error is:
    > >
    > > Range("B19").Validation.Modify xlValidateList, xlValidAlertStop,

    > xlBetween,
    > > "=$L$6:$L$12"
    > >
    > > Cell B19 is a pull-down list created using the tool>validation function

    > and
    > > being adjusted using the above VBA statement and "=$L$6:$L$12" is the

    new
    > > list source lookup range.
    > >
    > > Any help with the problem will be appreciated.
    > >
    > > Thanks in advance.
    > >
    > >

    >
    >
     
    Kent Klingler, May 13, 2004
    #3
  4. You can use the UserInterfaceOnly property of the Protect method like

    Sheets("YourSheet").Protect UserInterfaceOnly:=True
    'your code here

    But your current workaround is ok too. And with the sheet protected you
    won't be able to modify the validation...

    --
    Regards

    Juan Pablo González

    "Kent Klingler" <> wrote in message
    news:40a39b90$...
    > Thanks, but removing the xlBetween constant didn't work for me, I still

    get
    > the run-time error. I did find a work-around by unprotecting the

    worksheet
    > prior to executing the VBA statement. And then re-protecting the

    worksheet
    > after executing the VBA statement. Not what I would call graceful,
    > certainly functional, and unfortunately leaves the worksheet unprotected

    to
    > unwanted data entry for a split-second.
    >
    > I'm certainly open to other suggestions, if there are any.
    >
    > "Juan Pablo González" <> wrote in message
    > news:...
    > > Remove the xlBetween constant.
    > >
    > > Range("B19").Validation.Modify xlValidateList, xlValidAlertStop, ,
    > > "=$L$6:$L$12"
    > >
    > > worked fine for me
    > >
    > > --
    > > Regards
    > >
    > > Juan Pablo González
    > >
    > > "Kent Klingler" <> wrote in message
    > > news:...
    > > > I've developed a fairly simple spreadsheet for the purpose of

    collecting
    > > > information. In the worksheet are several pull-down lists created

    using
    > > the
    > > > data>validation>list function in Excel. Based on the selection in one

    > of
    > > > pull-down, other pull-down list source ranges will be adjusted.
    > > >
    > > > Everything works fine when the worksheet is unprotected, but when I

    > > protect
    > > > the worksheet I get the following error message:
    > > >
    > > > Run-time error '1004';
    > > > Application-defined or object-defined error
    > > >
    > > > The code generating the error is:
    > > >
    > > > Range("B19").Validation.Modify xlValidateList, xlValidAlertStop,

    > > xlBetween,
    > > > "=$L$6:$L$12"
    > > >
    > > > Cell B19 is a pull-down list created using the tool>validation

    function
    > > and
    > > > being adjusted using the above VBA statement and "=$L$6:$L$12" is the

    > new
    > > > list source lookup range.
    > > >
    > > > Any help with the problem will be appreciated.
    > > >
    > > > Thanks in advance.
    > > >
    > > >

    > >
    > >

    >
    >
     
    Juan Pablo González, May 13, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chracatoa

    How to create two validation lists that modify each other's value?

    Chracatoa, Sep 6, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    250
    Tom Ogilvy
    Sep 6, 2004
  2. Guest

    Problems with Validation object Add and Modify methods

    Guest, Jun 26, 2006, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    187
    Guest
    Jun 26, 2006
  3. Replies:
    2
    Views:
    592
  4. chatterbox

    Handling Worksheet Events of a Worksheet Created During Runtime.

    chatterbox, Apr 25, 2007, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    290
    Guest
    Apr 25, 2007
  5. Guest

    Validation error when worksheet is protected

    Guest, Sep 7, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    353
    Guest
    Sep 7, 2007
  6. KES
    Replies:
    0
    Views:
    257
  7. Raj

    Protected sheet: Automation error for validation

    Raj, May 29, 2008, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    226
  8. StargateFan

    How to get worksheet change code to work on protected worksheet?

    StargateFan, Feb 16, 2009, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    432
    StargateFan
    Feb 16, 2009
Loading...