Automation error with validation object
 

Hi,

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

Sub rProducts()
Application.Goto
Reference:=Worksheets("ProductMaster").Range("a4:a 65536")
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

Automation error with validation object
 

On May 28, 1:11*pm, Raj wrote:
Hi,

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

Sub rProducts()
* * * *Application.Goto
Reference:=Worksheets("ProductMaster").Range("a4:a 65536")
* * 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").Val idation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub