Solved

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

Posted on 2011-09-21
9
289 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
  • 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
 

Author Comment

by:Pdeters
ID: 36574614
oops- i see that is what this is doing. sorry kgerb.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now