You forgot the validation type.

With ActiveSheet.Range("myrange")
.Validation.Delete
.Validation.Add xlValidateList, xlValidAlertStop, , "=mbe"
.Validation.InCellDropdown = True
.Validation.IgnoreBlank = True
End With


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jigs d" wrote in message
...
hi all,
i want to create dynamic validation list in excel. The logic shd be like
this.
i m fetching data from SAP on the click of execute button in sheet2.
at this point of time i want to create dynamic validation list in sheet2
in
row "E". and data to be filled in the list will be fetched from sheet1.
i have added new named range in sheet1 called"mbe"(in sheet1!A)

when user will execute excel i will feel data in sheet1 cloumn A.
and i have written this code in the click of execute button :
With ActiveSheet.Range("myrange")
.Validation.Delete
.Validation.Add xlValidAlertStop, , "=mbe"

.Validation.InCellDropdown = True
.Validation.IgnoreBlank = True
End With

its giving runtime error "1004" (object defined error)
please help me to solve this problem

thanks in advance,
jigs