Thanks for the pointers, much appreciated.

Murray

-----Original Message-----

Both these worked for me:

Sub AAAValidation()
With Selection.Validation
Dim sRange As String
sRange = "=$D$60:$D$83"
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=sRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Use a defined Name (dynamic range):


Sub AAAValidation()
With Selection.Validation
Dim sRange As String
ThisWorkbook.Names.Add Name:="List", _
RefersTo:="=Offset($D$60,0,0,COUNTA
($D$60:$D$100),1)"
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=List"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

--
Regards,
Tom Ogilvy

Murray Taylor wrote in
message
...
I am trying to write a macro that resets the range used
by
a Data Validation input box in a cell. I have the
validation set on a range of cells and elsewhere in the
sheet there is the list of valid input. I change the
list
and need to programmatically change the validated cells,
but the following code doesn't seem to accept a named
range

With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$D$60:$D$83"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I need to be able to change the abolsute range reference
following Formula1:=

Any pointers much appreciated.

Murray


.