none
How to customise icon Excel toolbar using XLL API from C++ RRS feed

  • Question

  • Hi,

    I'm writing an XLL and I have an old-style toolbar (added using Excel4(xlcAddToolbar)), but I'm having trouble changing the icon from the default smiley face.  I do understand that this API is not recommended for use any longer.  In the medium term, I will move to the COM/RibbonX method.  However, as that will require me to do quite a bit of C++ COM work to bridge between my XLL and COM, I am looking for a quick fix to this issue.

    My current understanding is that when I call xlfAddToolbar via the C Excel4() API, I pass in an array of parameters:

    __declspec(dllexport) void AddToolbar () {
    	XLOPER12 xTest;
    	Excel12f (xlfGetToolbar, &xTest, 2, TempInt12 (1), TempStr12 (L"XLADDIN"));
    	if (xTest.xltype == xltypeErr) {
    		XLOPER12 xlaToolRef[9];
    		XLOPER12 xlArr;
    		xlArr.xltype = xltypeMulti;
    		xlArr.val.array.columns = 9;
    		xlArr.val.array.rows = 1;
    		xlArr.val.array.lparray = &xlaToolRef[0];
    		xlaToolRef[0].xltype = xltypeStr;
    		xlaToolRef[0].val.str = TempStr12 (L"211")->val.str;
    		xlaToolRef[1].xltype = xltypeStr;
    		xlaToolRef[1].val.str = TempStr12 (L"Settings")->val.str;
    		xlaToolRef[2].xltype = xltypeStr;
    		xlaToolRef[2].val.str = TempStr12 (L"FALSE")->val.str;;
    		xlaToolRef[3].xltype = xltypeStr;
    		xlaToolRef[3].val.str = TempStr12 (L"TRUE")->val.str;
    		xlaToolRef[4].xltype = xltypeStr;
    		xlaToolRef[4].val.str = TempStr12 (L"943")->val.str; // Gears face (face means icon in office-speak)
    		xlaToolRef[5].xltype = xltypeStr;
    		xlaToolRef[5].val.str = TempStr12 (L"Settings desc")->val.str;
    		xlaToolRef[6].xltype = xltypeStr;
    		xlaToolRef[6].val.str = TempStr12 (L"")->val.str;
    		xlaToolRef[7].xltype = xltypeStr;
    		xlaToolRef[7].val.str = TempStr12 (L"")->val.str;
    		xlaToolRef[8].xltype = xltypeStr;
    		xlaToolRef[8].val.str = TempStr12 (L"")->val.str;
    		int retVal = Excel12f (xlfAddToolbar, NULL, 2, TempStr12 (TEXT ("XLADDIN")), &xlArr);
    		LOGTRACE ("xlfAddToolbar retval = %d", retVal);
    		Excel12f (xlcShowToolbar, NULL, 10, TempStr12 (L"XLADDIN"), TempBool12 (1),
    			TempInt12 (5), TempMissing12 (), TempMissing12 (), TempInt12 (999), TempInt12(0), // no protection, 
    			TempBool12(TRUE), TempBool12(TRUE), TempBool12(TRUE));
    	}
    	Excel12f (xlFree, 0, 1, &xTest); 
    }

    I understand that the 5th parameter is a 'Face graphic reference' or 'reference to a defined picture object'.  From my research I've found that 'faceids' are enumerations referring to the build in icon set in excel.  I have also seen code that allows you to load an image onto the clipboard and 'pasteface' into a COM representation of the (old) toolbar objects.  But I'm not clear on what I should pass via this parameter.  

    I've looked up the number of a face I want (some cogs, face 943 would be acceptable), I've tried passing as a string, integer or number, but nothing seems to work.

    Can anyone enlighten me?  I do appreciate that it's an obsolete API, but it must have worked at some point...

    Thanks,

    Jim

    Monday, August 22, 2016 2:06 PM

Answers

  • Hi Jim Moores,

    According to your description, since the issue is complex, I suggest you contacting Microsoft support to raise an incident so that our engineer could work closely with him to identify the root cause and resolve this issue as soon as possible.
    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. 

    Please visit the below link to see the various paid support options that are available to better meet your needs. 

    https://msdn.microsoft.com/subscriptions/bb266240.aspx

    Thanks for your understanding.
    • Marked as answer by Jim Moores Wednesday, August 24, 2016 9:41 AM
    Wednesday, August 24, 2016 9:34 AM

All replies

  • Hi Jim Moores,

    According to your description, in earlier versions of Excel, you could use XLL code to customize menu bars, menus, command bars, or toolbars using commands such as xlcAddBar, xlcAddMenu, xlcAddCommand, xlcShowBar, xlcAddToolbar, xlcAddTool, and xlcShowToolbar. These commands are still supported but, because the old menu-bar and command-bar structures are replaced, they place access to your XLL commands in the Add-in group of the Ribbon and therefore may not give your users the intended interface.

    You can only customize the UI in the 2007 Microsoft Office release using managed code. One approach to UI customization in Excel 2007 is to have a separate managed code resource or add-in, within which the functions that customize the UI reside. You can then tightly couple it to your XLL, calling back into your XLL code to invoke the commands and functions it contains.

    For more information, click here to refer about Developing Add-ins (XLLs) in Excel 2007


    Tuesday, August 23, 2016 5:34 AM
  • Hello David

    Thanks for your reply.  I do have the Steve Dalton book, so I've read the section you've included, I've also looked through all the online docs, the SDK, various Google queries and searched all of GitHub for any plug-ins that try to do what I'm doing.

    I was really looking for some behind-the-scenes knowledge of in how to solve the problem I've listed, namely how to use xlfAddToolbar to add a toolbar with a custom (or, failing that, an existing) icon.  Do you think I would get any further by opening a paid-for incident?

    Jim

    Tuesday, August 23, 2016 1:46 PM
  • Hi Jim Moores,

    According to your description, since the issue is complex, I suggest you contacting Microsoft support to raise an incident so that our engineer could work closely with him to identify the root cause and resolve this issue as soon as possible.
    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. 

    Please visit the below link to see the various paid support options that are available to better meet your needs. 

    https://msdn.microsoft.com/subscriptions/bb266240.aspx

    Thanks for your understanding.
    • Marked as answer by Jim Moores Wednesday, August 24, 2016 9:41 AM
    Wednesday, August 24, 2016 9:34 AM
  • Thank you for your help.
    Wednesday, August 24, 2016 9:41 AM