Results 1 to 5 of 5

Thread: Ribbon identifiers for Solver

  1. #1

    Ribbon identifiers for Solver



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Could you please help me identify the ID for the Solver add-in button and the group ID for the Analysis group in the Data tab in the ribbon in Excel 2010? Even though Solver comes with Excel, I didn't see those ID's in the list of "Office Fluent User Interface Control Identifiers" available from Microsoft (perhaps because Solver is developed by a third-party).

    My reason for looking for the ID is that I would like to specify an onAction callback using XML/ RibbonX in the customUI.xml file (google "Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)" and/or "Change the Ribbon in Excel 2007 or Excel 2010" for more info on what I generally hope to do).

    My goal is to allow my own VBA code to run when a user selects the Solver button in a protected worksheet (normally Solver can't be run in a protected worksheet). My own VBA code would un-protect the worksheet, run Solver, then re-protect the worksheet. I know disabling/enabling protection like this isn't fool-proof, but for my needs, it's better than nothing.

    Beyond using some existing list that shows what the IDs are, is there a tool / method that can be used to show ribbon IDs?

    Thank you for your help!

  2. #2
    To elaborate on what I am trying to do...

    Using the "Custom UI Editor for Microsoft Office", I can specify customUI.XML like this:
    Code:
    <customUI xmlns="I CAN'T INCLUDE LINKS HERE BECAUSE I DON'T HAVE 5 POSTS, BUT IT'S THE STANDARD URL"> 
        <commands>
            <command 
                idMso="Cut" 
                enabled="true"
                onAction="ThisWorkbook.MyMacro"
                />
        </commands>
    </customUI>
    This creates a callback so that when a user selects the Cut button in the ribbon, my own macro is called instead of using the normal cut functionality. I am thinking that because Solver is not a Microsoft product (even though it is included with Excel), the "Solver" button that one sees in the Analysis group of Data tab would not have an idMso attribute, but instead use an id attribute. Is there some sort of id / idMso that I can specify that would allow me to override Solver's normal functionality via a customUI.XML like the above? As noted in the original post, my intent is to unprotect the worksheet, run Solver, and then re-protect the worksheet.

    Unfortunately, I'm not even sure it's a command element that would be used. Maybe a button element? Maybe a control element?

    I didn't see anything Solver-related in the list of Microsoft ribbon elements (google "Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)"). Is there a tool that reverse-engineers the list of MS and third-party commands / controls / elements within the ribbon?

    Thank you for your help!

  3. #3
    I'm still trying to figure out how to repurpose the Ribbon UI elements for the third-party Solver add-in for Excel 2007 and Excel 2010. I found that I could open the XML for the "C:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM" file in the "Custom UI Editor for Microsoft Office":
    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?><customUI xmlns="link removed" xmlns:excelAddin="link removed">
        <ribbon>
            <tabs>
                <tab idMso="TabData">
                    <group idQ="excelAddin:groupExcelAddins" label="Analysis" insertAfterMso="GroupOutline">
                        <button insertBeforeQ="excelAddin:spacer" id="btnSolver" getLabel="SOLVER.XLAM!GetSolverLabel" image="rId1" onAction="solver.xlam!MainEx" size="normal" screentip="Solver" supertip="What-if analysis tool that finds the optimal value of a target cell by changing values in cells used to calculate the target cell."/>
                    </group>
                </tab>
            </tabs>
        </ribbon>
    </customUI>
    When I change the onAction to be a macro of mine in the customUI.xml file, it adds a second button the Analysis group in the Data tab rather than repurposes the existing Solver button. If I set the visible attribute to false, the third-party add-in's button still appears. Those two things make me think that the XML for the Solver addin is run after the customUI.xml that I create. Could someone please confirm that this is true? If it is true, is there some way that I can configure something or run VBA code to load or create my interface definition last so that it will re-purpose the Solver button from what the third-party add-in specifies rather than create a second button?

    Thanks for your help.

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    That XML will always create a new control. You need to use the <commands> attribute to repurpose controls and that will not accept id/idQ for the command you specify - only idMso. You might be able to hide the entire group and replace it with your own (I have not tested that theory).
    Circumference of a circle = 2πr²



    ²the circle's radius

  5. #5
    I'm still experimenting with it, but your idea of hiding the group and replacing it with my own seems to work. Thank you for the tip!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •