VB and VBA Users Source Code: Execute an office command bar button (menu item)

VB/VBA Source Code
Execute an office command bar button (menu item)

By:	Andrew Baker
Email (spam proof):	Email the originator of this post
Date:	Wednesday, November 08, 2000
Hits:	1102
Category:	Unspecified

Article:
To click or execute a menu item in an office application use the following code. Note, a demonstration routine at the bottom of this post clicks the "File", "Save" menu control. Option Explicit 'Purpose : Executes a command bar button 'Inputs : oCommandBar The command bar containing the control to execute. ' sMenuCaption The caption on the menu holding the control. ' sMenuItem The caption on the control to execute. 'Outputs : Returns True if the control was executed. 'Author : Andrew Baker 'Date : 08/11/2000 10:52 'Notes : 'Revisions : Function ExecuteButton(oCommandBar As CommandBar, sMenuCaption As String, sMenuItem As String) As Boolean Dim oItem As CommandBarPopup, oSubItem As CommandBarButton For Each oItem In oCommandBar.Controls If oItem.Caption = sMenuCaption Then For Each oSubItem In oItem.Controls If oSubItem.Caption = sMenuItem Then ExecuteButton = True oSubItem.Execute Exit For End If Next End If If ExecuteButton Then Exit For End If Next End Function 'Demonstration routine Sub SaveWorkbook() If ExecuteButton(Application.CommandBars(1), "&File", "&Save") Then MsgBox "Workbook Saved!", vbExclamation End If End Sub
Terms and Conditions
Support this site Download a trial version of the Excel Workbook Rebuilder