Solved

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

Posted on 2011-09-21
9
295 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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