Range Reference

[Murray Taylor]

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

 

[JE McGimpsey]

This works for me:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="=myrng"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

where myrng is the named range.

However, I wonder if you would do better to just set the validation
range to a named range, but make that named range dynamic (See

http://cpearson.com/excel/excelF.htm#DynamicRanges

for how to set up dynamic ranges).

 

[Tom Ogilvy]

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

 

[Murray N Taylor]

Thanks for the pointers, much appreciated.

Murray

 

[Murray N taylor]

Thanks for that, the help is much appreciated.

Murray