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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> >
>
>

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" <(E-Mail Removed)> wrote in message
news:40a39b90$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.
> > >
> > >
> >
> >
>
>