Data Validation list in a cell through VBA

[acsishere]

Dear Friends,

I have assigned a "Named Range" using Offset function. The Name of that Named range is - "Year".

I wish to create "DATA VALIDATION LIST" in a cell (like in B1, F1, M1) using that Named Range "Year" using macro.

Therefore, request dear friends to help me.

[BenMcBen]

Where are you stuck/how far have you got?

[BenMcBen]

Attachment 718030
Attachment 718031

You should be able to figure this out by recording macros - I would also avoid using names that might be reserved words (eg "Year") in anything...

[acsishere]

Sir,

First, the link in that attachments are not working.

Secondly, I wish to run a macro that create a "Data validation list" in specific cells.

When browsed, I found a code, somewhat suitable:

Please Login or Register  to view this content.

If I run, the

Please Login or Register  to view this content.

is showing error.

Can any one please help me to modify the code?

[rorya]

You'd replace the =Animals with =Year but otherwise the code is ok (note that the Formula1 part is not a separate line of code - it's actually part of the previous line due to the underscore at the end of that line). It would be safer to delete any existing validation first using:

Please Login or Register  to view this content.

[acsishere]

I corrected the code. Still showing error 1004:

Please Login or Register  to view this content.

My current macro is:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Pl let me know what correction needed.

[rorya]

Is the worksheet protected? Is the named range correctly defined?

[acsishere]

Yes, worksheet is protected with a password "a".

The named range is correctly defined.

[rorya]

If the worksheet is protected, you can't change the validation. You'll need to unprotect, make the change, then reprotect.

[acsishere]

If I completely unprotect the sheet, still the above code is showing that error #1004.

[rorya]

On which line?