dynamic validation list
 

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

dynamic validation list
 

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