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

Topic

New Reply

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+ ??:confused:

Reply | Quote

Replies

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.
34764-CustomToolBar
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

Brrrknee,

The only thing I can think of would be to add the commands you need to the QAT (Quick Access Toolbar). HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote