NamedRange validation is working only when validated cell is choosen

Hi there,

Without going into your code in detail, the first thing I would suggest investigating is the highlighted sections of the following code:

Please Login or Register  to view this content.

All of these refer to ranges on whichever worksheet happens to be active when your code is run, but it is much better to use fully-qualified references (e.g. Worksheets("SheetName").Range("RangeAddress")).


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

thank you.
But i do not have worksheet name while importing CSV.

Only named range so this is why i am using application.range property.
Still this is not solving the issue...

One workaround can be to retrieve worksheet name from named Range and refer directly but would be the best option?
It will not slow the macro performance?

Best,
Jacek

Anyone?

Jacek

hi,

please advice,
Jacek

Hi,

anyone?

Only way to avoid this selection is to refer to worksheet directly using:

Please Login or Register  to view this content.

True?

What with using application.goto ?
Maybe this will solve?

I tried to test but i got error.

Jacek

Please help,

Jacek

Ok i will stick method :

Please Login or Register  to view this content.

i do noth think so there is another workaround for this.

Without selecting i have to refer exact to specific worksheet where named range is.

I am not closing the topic - maybe somebody will help in the future.

Best,
Jacek

Hi, jaryszek
I don't quite understand what you're trying to do.
But if you want to get the sheet's name of a named range then you can use 'Parent.Name'.
Something like this:

Please Login or Register  to view this content.

This is a problem about Akuini.

I have CSV with all named ranges but without parent worksheet names.
And excel is not referencing with application.range (sometimes does, sometimes not) when specific worksheet is not selected.
So i have 2 options:

1. Go to specific worksheet using application.goto property
2. Add to csv worksheet name and use it with worksheets("sheet1") property.

I do not think other solutions.

Jacek

Hi Guys,

i have to refresh the topic.

I am testing solution with this loop:

Please Login or Register  to view this content.

so i am looping through all named ranges and checking validations there.
problem is when validation is false because in this line:

If .Range(vName.Name).Validation.Value = False

macro is stopping and exiting without any error!!!
Only way to avoid this is to use application.goto method. So i have to open specific worksheet.

Why this is working like that? How to catch this?
Worksheets("Name") is not working...

Please help,
Jacek

Ok Guys i found the problem.

This is only when i am getting "Value#" in data validation lists...

Jacek