Adding Validation (drop down)

[William Hearn]

Hello

I have created an XLL to register a set of functions within xl. The
return values of certain ones I wish to populate a drop down from. I
can successfully do this by attaching a toolbar and a button and
running the following on the on click event. (via the xl object model)

if (m_XLApplication)
{
Excel::RangePtr pRange = m_XLApplication->ActiveCell;
CComVariant vEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
CString strRange = (LPCSTR)_bstr_t
(pRange->GetAddress(VARIANT_TRUE, VARIANT_TRUE, Excel::xlR1C1,
VARIANT_FALSE, vEmpty));

Excel::ValidationPtr pValidation = pRange->Validation;
CString str = _T("Test1, Test2, Test3");
try
{
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;
}
catch (_com_error e)
{
OutputDebugString(e.Description());
}

This sucessfully creates the drop down entry.

When I attempt to drive this from a function that I have registered
i.e exactly the same code but called from inside an exported XLL
function. An exception is thrown with no error code or description, as
I attempt the 'add' for validation. The m_XLAppplication is the
active xl instance CComPtr<Excel::_Application> and I have tested
items such as the range address to make sure the application is valid
before attempting to add the Validation to the selected range.

 

[Tom Ogilvy]

If by function you mean you are trying to put the function in a cell, then
the problem probably is that Excel does not allow a function in a cell to do
anything but return a value to the cell - it can not change environmental
settings and so forth.

 

[Will]

Many thanks for your reply

My registered function in xl returns a string but inbetween it has access to the active sheet/active cell and attempts to create a dropdown combo containing a set of values, using the Validation object much in the same way the following vba code would do

Function GetSomeData() as strin

dim str as strin
' do some work to populate the strin

ActiveCell.Selec
ActiveCell.Clea
With Selection.Validatio
.Delet
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str
.IgnoreBlank = Tru
.InCellDropdown = Tru
.InputTitle = "
.ErrorTitle = "
.InputMessage = "
.ErrorMessage = "
.ShowInput = Fals
.ShowError = Tru
End Wit
GetSomeData = GetFirstString(str) '
End Function

 

[Tom Ogilvy]

and you could not do

=Getsomedata()

in a cell with that function - it would just return #Value

I don't know if that is what you are trying to do, but if it is, it won't
work. At least it won't work with this VBA function. If an xll can get
around that limitation, I can't say, but if you are having problems and
attempting that, then I would think it can't.

--
Regards,
Tom Ogilvy

Many thanks for your reply

My registered function in xl returns a string but inbetween it has access

to the active sheet/active cell and attempts to create a dropdown combo
containing a set of values, using the Validation object much in the same
way the following vba code would do.

Function GetSomeData() as string

dim str as string
' do some work to populate the string

ActiveCell.Select
ActiveCell.Clear
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,

Operator:=xlBetween, Formula1:=str