Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Lists: Allow option of adding own data if not in drop do | Excel Worksheet Functions | |||
permenent appearance of the drop down arrow when adding validation toa cell | Excel Worksheet Functions | |||
Adding data validation | Excel Discussion (Misc queries) | |||
Validation Drop Down Box | Excel Discussion (Misc queries) | |||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell? | Excel Programming |