Solved

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

Posted on 2011-09-21
9
290 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Graph of User occurance 9 27
Create Excel formula on dynamic data 5 32
Fixing a embedded format 7 29
ADD New Entries 7 15
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

919 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

12 Experts available now in Live!

Get 1:1 Help Now