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. Replies:
    2
    Views:
    354
  2. chatterbox

    Handling Worksheet Events of a Worksheet Created During Runtime.

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

    Validation error when worksheet is protected

    Guest, Sep 7, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    263
    Guest
    Sep 7, 2007
  4. KES
    Replies:
    0
    Views:
    202
  5. StargateFan

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

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

Share This Page