Solved

Excel 2003 VBA error Method 'add' of object 'valadation' failed

Posted on 2011-09-21
9
301 Views
Last Modified: 2012-05-12
I have an Excel 2003 worksheet that I have inherited.

There are drop downs that depending on what is selected in one gives different options for another cell that has a drop down.

When I select on drop down and then go to the next it i am getting the error
Excel 2003 VBA error Method 'add' of object 'valadation' failed or run-time error 1004 application-defined or object- defined error.

The dropdowns data is referened on another sheet in the work book. any ideas where to start?

the error comes up at .Add in the following code

                   With Range("E24").Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=strPGList
                        .IgnoreBlank = True
                        .InCellDropdown = True
     
0
Comment
Question by:Pdeters
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36574372
What is strPGList?
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36574431
Take a look at this example.  It changes the contents of one validation list based on another.  It works and might give you an idea on how to change yours to get it working.

Kyle
Q-27319958-RevA.xlsm
0
 

Author Comment

by:Pdeters
ID: 36574605
What I am looking for is when something is selected in one drop down the next drop down is depended on what is selected in first drop down.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Pdeters
ID: 36574614
oops- i see that is what this is doing. sorry kgerb.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36574634
No problem, do you understand how it works?  It takes advantage of named ranges.  Let me know if you have questions.

Kyle
0
 

Author Comment

by:Pdeters
ID: 36574728
I have the name ranges and all that is good. Is this a naming in the validation box of the second drop down? or is it refering to just the cell J5

=INDIRECT(J5)

0
 
LVL 12

Expert Comment

by:kgerb
ID: 36574777
The INDIRECT function makes the second validation drop down look for the range from the name in cell J5.  Without the INDIRECT function the second validation would populate with the value in J5, not the name of the range in J5.
0
 

Author Comment

by:Pdeters
ID: 36574850
So when I am getting the error here

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=strPGList


the drop down list strPGList has an invalid reference some how - correct?
0
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 36575225
Pdeters,
I don't know enough information to tell you exactly what is happening.  If strPGList is the name of a range then you need to do something like this:

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:="=INDIRECT(strPGList)"
or
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:="=" & strPGList

I was wrong about needing the indirect function.  As long as you concatenate the "=" it works fine.  However, it shouldn't throw an error unless, like you said, there is something wrong with the named range.

Can you post a workbook.  It might make it easier to see what's happening.

Kyle
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question