Dear all experts,

I create a named validation list called "chuen_PN" via marco recording on
worksheet1 with the similar code like following:
.......
Range("A1:C12").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=chuen_PN"
.IgnoreBlank = True
.InCellDropdown = True
......
......
......

Remark * ( soruce of chuen_PN is in different worksheet of the same workbook
..)

Now I want to use VBA code to point the range instead of the named
(chuen_PN) , but it's failed when I change the code like following..

......
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="worksheet(index).range("A1:A12")"
.IgnoreBlank = True
.InCellDropdown = True
......

Can I define a range by VBA code (worksheet(index).range......) in
validation list here?

Thanks all

Norika,
it's not a programming problem:

data validation does not allow a range on a different worksheet.
you MUST go via a defined name aka "named range".

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


norika wrote :

Dear all experts,

I create a named validation list called "chuen_PN" via marco
recording on worksheet1 with the similar code like following:
......
Range("A1:C12").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=chuen_PN"
.IgnoreBlank = True
.InCellDropdown = True
......
......
......

Remark * ( soruce of chuen_PN is in different worksheet of the same
workbook .)

Now I want to use VBA code to point the range instead of the named
(chuen_PN) , but it's failed when I change the code like following..

......
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="worksheet(index).range("A1:A12")"
.IgnoreBlank = True
.InCellDropdown = True
......

Can I define a range by VBA code (worksheet(index).range......) in
validation list here?

Thanks all

If the DV is pointing to another worksheet, you have to use a name to
reference through. If it is not, you don't need the worksheet name.

--
HTH

Bob Phillips

"norika" wrote in message
...
Dear all experts,

I create a named validation list called "chuen_PN" via marco recording on
worksheet1 with the similar code like following:
......
Range("A1:C12").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=chuen_PN"
.IgnoreBlank = True
.InCellDropdown = True
......
......
......

Remark * ( soruce of chuen_PN is in different worksheet of the same
workbook
.)

Now I want to use VBA code to point the range instead of the named
(chuen_PN) , but it's failed when I change the code like following..

......
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="worksheet(index).range("A1:A12")"
.IgnoreBlank = True
.InCellDropdown = True
......

Can I define a range by VBA code (worksheet(index).range......) in
validation list here?

Thanks all