Range Reference
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
.
|