I've developed a fairly simple spreadsheet for the purpose of collecting
information. In the worksheet are several pull-down lists created using the
datavalidationlist 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 toolvalidation 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" wrote in message
...
I've developed a fairly simple spreadsheet for the purpose of collecting
information. In the worksheet are several pull-down lists created using
the
datavalidationlist 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 toolvalidation 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" wrote in message
...
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
...
I've developed a fairly simple spreadsheet for the purpose of collecting
information. In the worksheet are several pull-down lists created using
the
datavalidationlist 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 toolvalidation 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" wrote in message
...
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
...
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
...
I've developed a fairly simple spreadsheet for the purpose of
collecting
information. In the worksheet are several pull-down lists created
using
the
datavalidationlist 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 toolvalidation
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.