VBA Data Validation with Objects

[JohnGaltnl]

Dear users,

I have an excel sheet that activates a macro to create a data validation list when a certain area is activated by double clicking.
This datavalidation list is based on the value to cell to the left of where the validation list will appear.
The formula looks like this:

Please Login or Register  to view this content.

Because the cell G7 wil change for each data validation to the row where the validation list is created i tried to adapt the code to what you see below. But unfortunately it does not work and gives me an error on the Offset formula line (object defined error). I'm quite new to this and can't figure out how to solve this.
I hope some of you have an answer to this.

Please Login or Register  to view this content.

[Leith Ross]

Hello John,

This modification to your code should work.

Please Login or Register  to view this content.

[JohnGaltnl]

Dear Leith,

Thanks for your reply. It seems now that the Named Ranges I use are probably the problem. The code still isn't working because of object defined errors. When i replace the CustomerName with a cell like G7 and I run the code I still get this error. I know, should have tested this before :S

So I think I have to change something with the named ranges I use as well. You maybe know what this is?

[Leith Ross]

Hello John,

Are the named ranges global or local? Local means the named range must include the worksheet name when referenced.

[DonkeyOte]

VBA is US-centric unless explicitly told to do otherwise (e.g. FormulaLocal vs Formula)

In this particular instance given no Formula1-Local equivalent exists you should revert to US delimiters in your VBA

Please Login or Register  to view this content.

Excel will automatically adjust the delimiters accordingly upon execution (per locale).

You may also wish to add a Cancel = True
(pending personal preference of subsequent action of course)

[JohnGaltnl]

Thanks DonkeyOte. That fixed it!