Adding Validation (drop down)
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. |
Adding Validation (drop down)
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. -- Regards, Tom Ogilvy "William Hearn" wrote in message om... 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. |
Adding Validation (drop down)
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 |
Adding Validation (drop down)
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 "Will" wrote in message ... 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 .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = True End With GetSomeData = GetFirstString(str) ' End Function |
All times are GMT +1. The time now is 09:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com