Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ribbon removes VBA ability to find and "press" command-bar button

    I've several automations in the pre-ribbon era that worked very well, scanning for the proper command bar, then for the button within that bar, and activating the action 'pressing' to automate detailed actions.
    Ribbon UI now removed that ability to find and 'press' a COMMAND BAR button. Many MS MVPs had responded to my inquiries that they know of this horrid "chopping off" of functionality, and no MS response to address this shortsighted amputation. And even more horrendous is the MS offer of the backwards, complex, bloated duct-tape means of misusing the UI testing tool to screen-scrape the actions, investigate all over the resulting 'scrape' and hopefully find the button holding the MS command you need to perform.
    Has anyone found a reliable, non-UI test screen-scraping means to effect the previously 2-VBA LOC access of a command bar then pressing its proper button, in Office 2007/2010/2013+ ??

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brrrknee,

    I'm a little confused? By Command Bar do you mean Custom Tool Bar? If so they should show up on the Add-ins tab.
    CustomToolBar.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    no, not custom toolbars that we create, the simple means of accessing any toolbar by name and then the buttons therein... for example, the org chart command bar and its re-org || center button.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brrrknee,

    The only thing I can think of would be to add the commands you need to the QAT (Quick Access Toolbar). HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this requires per user adaptation of the specific environment, anyone using the tool having to modify their environment. Cannot do this nor automate this for the same reasons, cannot connect to previously accessible functionality.

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    If the purpose of clicking the un-named button is to run a named macro, why doesn't your code simply call that macro and cut out the middle man.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    there isn't an unnamed macro. its the MS code reacting to the MS command bar button, as in my example above, the org chart re-org centered button.

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    The MS command bars execute MS commands. If you want to simulate pressing one of those buttons then call the same command as MS do when you click that button.
    CommandBars.FindControl(Id:=IDnumber).Execute

    To find a command's id you can type into the Immediate Window something like this
    ?Application.CommandBars("Menu Bar").Controls(5).Controls(6).ID
    along with
    ?Application.CommandBars("Menu Bar").Controls(5).Controls(6).Caption

    You may even be able to extend this to actually simulate clicking on the button you've chosen (I haven't tested this)
    Application.CommandBars("Menu Bar").Controls(5).Controls(6).Execute
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Part of the ribbon issue is that prior "Add Ons" like the Org Chart tool, were able to create and manage a command bar. Fluent UI doesn't show add-ons' command bars (109 in my installation, none are Org Chart). OrgChart and its related functions are only seen as Add-ons, with a name and an index (98 of them!). No additional info.

    The Fluent UI BROKE a working programmatic means of working with the Org Chart command 'buttons.'

    Without the command interface I was forced to resort to using debug captures of events for selected tabs and menus and entries on menus, then using that hint, when needed I would need to 'fire' the command as though it were the selected add-on menu selection. not easy to explain. slightly akin to your ?App investigation via immediate window, but there are no "commands" accessible via VBA to the add on. just a PITA.

  10. #10
    Lounger
    Join Date
    Nov 2008
    Location
    Sydney, New South Wales, Australia
    Posts
    33
    Thanks
    0
    Thanked 1 Time in 1 Post
    You could distribute a custom UI for the QAT in STARTUP for all users - one template if you so wish. The users can then still add their own QAT requirements and have that template backed up.
    Kind regards
    janinecrutch.com

  11. #11
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    creating additional layers and responsibilities for each user is not a good solution, especially when there is no command to directly invoke.
    All of this thrashing, and in the pre-ribbon it was a simple few lines of VBA... MS sometimes doesn't think things through

  12. #12
    New Lounger
    Join Date
    Dec 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with add-in button IDs

    Quote Originally Posted by brrrknee View Post
    Part of the ribbon issue is that prior "Add Ons" like the Org Chart tool, were able to create and manage a command bar. Fluent UI doesn't show add-ons' command bars (109 in my installation, none are Org Chart). OrgChart and its related functions are only seen as Add-ons, with a name and an index (98 of them!). No additional info.

    The Fluent UI BROKE a working programmatic means of working with the Org Chart command 'buttons.'

    Without the command interface I was forced to resort to using debug captures of events for selected tabs and menus and entries on menus, then using that hint, when needed I would need to 'fire' the command as though it were the selected add-on menu selection. not easy to explain. slightly akin to your ?App investigation via immediate window, but there are no "commands" accessible via VBA to the add on. just a PITA.
    Dear brrknee,
    would you be so kind to explain the method with which you managed to "press" a button of an add-in? I would need to automatize pressing two buttons of the ExcelPython add-in (using Excel 2010), but failed so far.
    Thank you very much for the help in advance!
    Cheers,
    Peter

Posting Permissions

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