Automation error with validation object

[Raj]

Hi,

I was using the following code to create a validation list in column
A:

Sub rProducts()
Application.Goto
Reference:=Worksheets("ProductMaster").Range("a4:a65536")
ActiveWindow.ScrollRow = 1
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

This was working fine. Suddenly, it has stopped working and
"Automation Error" is displayed. When debug is clicked, the line
beginning ".Add" is highlighted.

Please help with a solution.

PS: I had posted this earlier and Bob Phillips had suggested to check
whether the named "categories" existed. It does exist.

Is there any other way of adding a validation list in a range using a
named category or range?. As the validation list range is dependent on
a value in another cell of another sheet, I need to do this through
VBA

Regards,
Raj

 

[JW]

Hi,

I was using the following code to create a validation list in column
A:

Sub rProducts()
       Application.Goto
Reference:=Worksheets("ProductMaster").Range("a4:a65536")
    ActiveWindow.ScrollRow = 1
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
        xlBetween, Formula1:="=categories"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

This was working fine. Suddenly, it has stopped working and
"Automation Error" is displayed. When debug is clicked, the line
beginning ".Add" is highlighted.

Please help with a solution.

PS: I had posted this earlier and Bob Phillips had suggested to check
whether the named "categories" existed. It does exist.

Is there any other way of adding a validation list in a range using a
named category or range?. As the validation list range is dependent on
a value in another cell of another sheet, I need to do this through
VBA

Regards,
Raj

Ran this several times and had no problems.

Sub rProducts()
With Worksheets("ProductMaster").Range("a4:a65536").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub