Overriding Excel Commands

Using VBA, it is easy to override a built-in Excel command. All you need to do is to change the OnAction property of an existing button to point to a procedure that has your modification. Of course, you need to do this for the button as well as the menu item. Listing 7.9 shows how you might override the Save button in Excel using VBA.

Listing 7.9. Overriding the Save button in Excel with VBA Sub OverrideButton()

Dim myNewCommand As Office.CommandBarButton

Set myNewCommand = CommandBars("Standard").Controls(3)

myNewCommand.OnAction = "FileSave"

End Sub

Sub FileSave()

MsgBox ("Cannot save document.") End Sub

You cannot do this in VSTO because the OnAction property can point only to an Excel macro, and not to a method in your VSTO code. You could create callbacks into your VSTO code from VBA, as shown earlier in this chapter. An alternative is to use VSTO to write code that replaces the existing built-in button or toolbar with your own. For an example, see Chapter 6. Although the example shows you how to replace menu items and toolbar buttons in Word, the same principle applies to Excel.

There are many objects in the Excel object model that you can manipulate from VSTO. However, this chapter does not focus on the existing object model. In the remainder of this chapter, we take a closer look at the VSTO enhancements to some Excel objects and explain how you can use these enhancements in your Excel solutions using VSTO.

0 0

Post a comment