Range Reference
 

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

Range Reference
 

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).

In article ,
"Murray Taylor" wrote:

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

Range Reference
 

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

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


.

Range Reference
 

Thanks for that, the help is much appreciated.

Murray

-----Original Message-----
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).

In article ,
"Murray Taylor" wrote:

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
.