Validation data list problem

[Karlbirger]

Hi guys

Got debugger (bolded text) when I run this code which I have recorded:

Range("J6").Select
    With Selection.Validation
        .Delete
[B]       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _[/B]
[B]       xlBetween, Formula1:="=INDIRECT(I6)"[/B]
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Got Windows Vista and Excel 2007. Anyone have had any similar problem or knows what could be wrong with the code?

 

[Andrew Poulsom]

Have you tried selecting something from the dropdown in I6 before running your macro (assuming there is one of course)?

 

[Karlbirger]

I did now, and the result is still the same debug.

 

[Andrew Poulsom]

What's in I6 when you get the error? Is it the name of a range? If so what does that name refer to?

 

[Karlbirger]

It's a validation data list in I6 called Asset, containing three possible selections; Real_estate, Energy, or Other.

At the time I create the INDIRECT(I6) validation list the validation list in I6 is created, but has no info/value choosen in the cell yet.

 

[Andrew Poulsom]

The code will fail if Excel can't evaluate the formula:

=INDIRECT(I6)

so one of the items in the dropdown in I6 must be selected and that must be a named range.

 

[Karlbirger]

As far as I know I can't select one of the alternatives in I6 roll dowh list by macro, and it shouldn't be an issue for creating =INDIRECT(I6).

But if this is the issue, how do I choose one of the values in I6 through vba?

 

[Andrew Poulsom]

Like this?

Range("I6").Value = "Real_estate"