Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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

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
Pdeters
Asked:
Pdeters
  • 4
  • 4
1 Solution
 
StephenJRCommented:
What is strPGList?
0
 
kgerbChief EngineerCommented:
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
 
PdetersAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PdetersAuthor Commented:
oops- i see that is what this is doing. sorry kgerb.
0
 
kgerbChief EngineerCommented:
No problem, do you understand how it works?  It takes advantage of named ranges.  Let me know if you have questions.

Kyle
0
 
PdetersAuthor Commented:
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
 
kgerbChief EngineerCommented:
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
 
PdetersAuthor Commented:
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
 
kgerbChief EngineerCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now