PC Review


Reply
Thread Tools Rate Thread

Validation.Modify Runtime Error in Protected Worksheet

 
 
Kent Klingler
Guest
Posts: n/a
 
      13th May 2004
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.


 
Reply With Quote
 
 
 
 
Juan Pablo González
Guest
Posts: n/a
 
      13th May 2004
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.
>
>



 
Reply With Quote
 
 
 
 
Kent Klingler
Guest
Posts: n/a
 
      13th May 2004
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.
> >
> >

>
>



 
Reply With Quote
 
Juan Pablo González
Guest
Posts: n/a
 
      13th May 2004
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.
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit text format in non-protected cells in protected worksheet Bonnie Microsoft Excel Misc 1 19th Apr 2008 04:48 PM
Re: Copying a worksheet witrh protected cells to a new worksheet Tiscali NewsGroup Microsoft Excel Worksheet Functions 0 1st Feb 2006 12:11 AM
Problem with protected Excel worksheet in protected Word document =?Utf-8?B?QW50aG9ueSBUcmF2aXM=?= Microsoft Excel Crashes 0 25th Jan 2005 08:21 PM
Problem with protected Excel worksheet in protected Word document =?Utf-8?B?YW50aG9ueXRyYXZpc0BSRU1PVkVfVEhJU19TVFVG Microsoft Excel Crashes 0 25th Jan 2005 07:49 PM
Visual c++ runtime Library runtime error. - c runtime error.jpg (0/1) ... Windows XP Accessibility 0 1st May 2004 06:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:31 AM.