Validation.Modify Runtime Error in Protected Worksheet
 

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.

Validation.Modify Runtime Error in Protected Worksheet
 

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.

Validation.Modify Runtime Error in Protected Worksheet
 

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.

Validation.Modify Runtime Error in Protected Worksheet
 

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.