Will,

When using a range on another sheet, it has to be a named range, that is
select the cells in that range, go into InsertNameDefine Name and give it
a meaningful name, then use this name (=myName) in the DV list.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Will" wrote in message
...
I am attempting to a drop down list to a cell which is populated from a
range of cells held on another sheet.

At the moment I am using the Validation method and can successfully
populate from a comma delimited string but not from a range that I have from
another sheet.

Excel::ValidationPtr pValidation = pRange-Validation;

pValidation-Delete();
pValidation-Add(Excel::xlValidateList, (CComVariant)xlValidAlertStop,
(CComVariant)Excel::xlBetween, (_variant_t)str);
pValidation-IgnoreBlank = VARIANT_TRUE;
pValidation-InCellDropdown = VARIANT_TRUE;
pValidation-InputTitle = OLESTR("");
pValidation-ErrorMessage = OLESTR("");
pValidation-ShowInput = VARIANT_FALSE;
pValidation-ShowError = VARIANT_TRUE;

Regards