Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

User Interface

How do I use VBA to add a simple button to the Ribbon?

You can't. You must write special XML code (known as RibbonX code) and insert the XML document into a workbook file by using third-party tools. Or, if you're a glutton for punishment (and know what you're doing), you can do it by unzipping the document and making the edits manually.

What are my options for modifying the user interface to make it easy for a user to run my macros?

In Excel 2007, you have these choices:

Modify the Ribbon (not an easy task).

Add your macro to the Quick Access Toolbar (a manual task that's not possible to perform using VBA).

Assign a shortcut key to the macro.

Add a new menu item to a shortcut menu.

Create an old-style toolbar or menu, which will display in the Add-Ins tab.

How do I add a macro to the Quick Access Toolbar?

Right-click the QAT and choose Customize Quick Access Toolbar from the shortcut menu. In the Customize tab of the Excel Options dialog box, choose Macros from the drop-down list on the left. Select your macro and click Add. To change the icon or text displayed, click the Modify button.

I added my macro to the QAT, but clicking the icon generates an error.

In order to execute a macro from the QAT, the workbook that contains the macro must be the active workbook. This is not the case, however, if the macro is in an add-in or stored in your Personal Macro Workbook.

How do I use VBA to activate a particular tab on the Ribbon?

SendKeys is your only choice. Press the Alt key to find out the keystroke(s) required. For example, to switch to the Page Layout tab, use this:

Application.SendKeys "%p{F6}"

How can I disable all the right-click shortcut menus ?

The following procedure will do the job:

Sub DisableAllShortcutMenus() Dim cb As CommandBar For Each cb In CommandBars If cb.Type = msoBarTypePopup Then _ cb.Enabled = False Next cb End Sub