The Selection Change Event

When you're manipulating content in a document or in your mailbox in Outlook, many things are happening under the covers in Office to reflect the current state of selected content. As discussed in the section of the previous chapter entitled "Selection," the most common selection you see is selected text. The text appears inverted as you select the characters of the text. You can tell that a shape or an object is selected by the sizing handles that appear at the corners and along the edges of the rectangle when it is selected.

In Excel, a thick black border around a range of cells represents the selection of that range of cells. Finally, in Outlook, the highlighted items in a folder in your mailbox represent a selection. Whenever you change where the selection handles appear, what text is inverted, what cells are selected in Excel, or what items are selected in your mailbox in Outlook, a selection change event occurs.

Word, Excel, PowerPoint, and Outlook each provide an event that handles a selection change. The chart below shows the different names used for the event procedure.

Office Application	Selection change event procedure
Word	WindowSelectionChange
Excel	SheetSelectionChange
PowerPoint	WindowSelectionChange
Outlook	SelectionChange

For each Office application—Word, Excel, PowerPoint, and Outlook—a list under the heading "When the Selection Change Event Triggers," indicates when the selection change event fires and discusses any differences among the applications. The most obvious difference among the selection change events in each application is the prefix used in the name of the event procedure. However, the events in each application all work towards the goal of trapping when the selection of content changes in the active document (or Explorer window in Outlook). In Word and PowerPoint, one argument—an instance of the Selection object—is passed to the event procedure in each application. Using the Selection object, you can determine what type of object is contained within the selection. The selection may be text or a shape, for example.

In Excel, two arguments are passed into the selection change event. The second argument is always a Range object. The selection change event in Excel fires only when the range of cells selected changes. In Outlook, no arguments are passed to the selection change event: you use the Selection property to determine the set of items selected in the active mailbox folder. Items can consist of mail, notes, appointments, or any other mailbox item found in Outlook.

One of the most common scenarios that the selection change event allows you to handle is the updating of the command bar controls based on selection. The classic example is the Bold button on the Formatting toolbar in Word, Excel, and PowerPoint. Whenever the active selection contains text that is all bold, the Bold button is in the depressed position. If the text isn't all bold, the button is in the up position. The Bold button is also disabled when the selection can't contain text, and it's enabled when the selection can contain text.

In this section, you'll create a custom Bold button and add it to the Standard toolbar in Word, Excel, and PowerPoint. The custom Bold button will mimic the behavior of the built-in Bold button on the Formatting toolbar—based on the current selection, it will appear in the depressed or up position and be disabled or enabled.

For each step under the descriptions of the selection change event in Word, Excel, and PowerPoint, you need to complete the following steps first. The code is generic because it uses the CommandBar object model and, as a result, works identically in Word, Excel, and PowerPoint.

1. In whatever application you're working with, Word, Excel, or PowerPoint, display the Visual Basic Editor and insert a new class module by clicking Class Module on the Insert menu. Add the following declaration:

Public WithEvents App As Application

2. Insert a new standard code module by clicking Module on the Insert menu. Add the following declaration and procedure to the top of the module:

Dim AppEvents As New Class1 Sub InitEvents() Set AppEvents.App = Application End Sub

As noted in the Quick Guide at the beginning of the chapter, you should use the class name "Class1," stated just after the New keyword in the declaration statement, as the name of the class module you added in the previous step. By default, the class module inserted is Class1, but you can change the name of the class module to anything you like.

3. After the InitEvents procedure, add the following procedure:

Sub InsertCustomBoldButton() With Application.CommandBars("Standard") _ .Controls.Add(msoControlButton) .Caption = "&CustomBold" .Tag = "CustomBold" .FaceId = 113 End With End Sub

In Word, Excel, and PowerPoint, you use this procedure to insert a custom Bold button at the end of the Standard toolbar. Note that the Tag property is set to "CustomBold" so your code can easily search for all instances of this control using the FindControls method. The FaceId property is set to the value of 113, which is the same FaceId value for the built-in Bold button. Your control will look exactly the same as the built-in Bold button on the Formatting toolbar.

4. Add the following procedure to the standard code module:

Sub SetButtonState( _ iButtonState As MsoButtonState, _ bEnabled As Boolean) Dim ctlItem As CommandBarControl Dim ctlColl As CommandBarControls Set ctlColl = Application.CommandBars _ .FindControls(Tag:="CustomBold") If Not ctlColl Is Nothing Then For Each ctlItem In ctlColl ctlItem.State = iButtonState ctlItem.Enabled = bEnabled Next ctlItem End If End Sub

You set the search criterion for the FindControls method to search for the tag "CustomBold" and use the FindControls method to return all instances of the custom bold button. Thus, as in the following steps, no matter how many times the custom bold button is copied to other places in the command bar set, the button state will always be applied to all instances of the control. That is, the For Each…Loop iterates through each instance of the custom Bold button and sets its state appropriately.

This procedure was copied from the steps "Searching for All Instances of a Built-In Control" in "The FindControls Method" section of Chapter 11. Its name is changed here from RenameBuiltInCommand to SetButtonState. The arguments iButtonState, declared as MsoButtonState, and bEnabled, declared as Boolean, were added within the Sub statement of the SetButtonState procedure.

5. Place the cursor in the procedure InsertCustomBoldButton and press F5 to run it.
6. Switch back to Word, Excel, or PowerPoint and note the custom Bold button at the end of the Standard toolbar. Click Customize on the Tools menu to get into command bar customization mode.
7. Holding down the CTRL key, click and drag a copy of the custom Bold button right beside the original copy of the custom Bold button. Repeat the same click-and-drag process to copy the button to any visible toolbar or to somewhere else on the Standard toolbar.

Now you're ready for Word, Excel, or PowerPoint to update a custom (or built-in) command bar control, based on the selection change. Once you complete the steps under the Word, Excel, and PowerPoint sections below and on the following pages, the selection change will cause the state of each instance of the custom Bold button to be up or down, enabled or disabled.

The previous chapter discussed how a selection in Word is defined as an insertion point in the text, highlighted text, or selection handles around any shape. A good way for you to visually determine if the selection has changed is by looking at the value of Col in the status bar at the bottom of the document window. The Col label is to the right of the "At" and "Ln" (Line) labels and indicates the column of text where the left side of a selection starts or ends. The size of the highlight of text or the presence of selection handles are other visual indicators. The following lists show when you can expect the selection change event to trigger and when you can expect the selection change event to not trigger.

• Selecting text with the mouse pointer and clicking between text. The event triggers when you let go of the left mouse button after selecting some text or clicking between text.
• Moving the insertion point (IP) with the arrow keys. The insertion point is the bar that is positioned either in between text or at the end of a line of text where you are about to type text.
• Double-clicking to select text. This causes the selection change event to trigger twice. The first click causes the event to trigger because it places the IP directly under the mouse pointer. The second click causes the event to trigger again because it causes the word containing the IP to be highlighted. When you double-click, the WindowBeforeDoubleClick event is also fired after the WindowSelectionChange event. See the section, "The Double-Click Event," later in this chapter for details.
• Right-clicking nonhighlighted text. A right click places the IP directly under the mouse pointer. Right-clicking highlighted text doesn't fire the event, because the selection doesn't change.
• Holding down the SHIFT key or CTRL key and pressing the arrow keys on your keyboard. Each time the text highlight is extended, the event triggers.
• Selecting a drawing shape.
• Deleting a floating shape.
• Selecting more than one shape. Each time you hold down the Shift key and select another shape, the event triggers.

• Typing text in Word. Unlike in PowerPoint, the event doesn't trigger when you type text, even though the cursor and Col value is changing. In PowerPoint, the selection change event triggers each time you enter a new character.
• Grouping or ungrouping shapes.
• Formatting text.

Before completing the following steps, complete the steps in the section "Update Your Controls Based on a Selection Change."

1. In the Visual Basic Editor started from Word, double-click the Class1 project item in the Project Explorer to make it the active window.
2. Click App from the Object drop-down list and then select Window-SelectionChange from the Procedures drop-down list in the class module. In the WindowSelectionChange event procedure, add the following code so that the procedure appears as follows:

Private Sub App_WindowSelectionChange( _ ByVal Sel As Selection) Select Case Sel.Type Case wdSelectionIP, wdSelectionNormal, _ wdSelectionBlock, wdSelectionColumn, _ wdSelectionRow If Sel.Font.Bold = True Then SetButtonState msoButtonDown, True Else SetButtonState msoButtonUp, True End If Case Else SetButtonState msoButtonUp, False End Select End Sub

One argument is passed to the WindowSelectionChange event in Word. As in PowerPoint, the argument is defined as type Selection. Using the Selection object in Word (and in PowerPoint), you can determine whether the selection is text, a shape, or any other selection type, as described in the previous chapter in the section entitled "Selection."

The Select Case statement in the WindowSelectionChange event procedure evaluates the selection type of the Sel object passed into the event procedure. If the selection is an insertion point, a continuous text selection, or a block, column, or row of text, an If…Then statement evaluates whether the selected text is bold. If it is, the SetButtonState procedure is called to set the custom Bold button to an enabled, depressed state. If the selection doesn't contain text, the custom Bold button is disabled and the state is set to "up."

3. Double-click the standard code module project item, Module1, in the Project Explorer to make it the active window. Place the cursor in the procedure InitEvents and press F5 to run the project.
4. Switch back to Word, add some text, shapes, and so forth, and format some text to bold in the active document.
5. Click the document's content, such as bold text, text without bold formatting, or a shape.

As you click around the content, the custom Bold button on the Standard toolbar—as well as any other place you copied the button—should toggle between the up and down (depressed) state and between enabled and disabled. The state of the custom Bold button should parallel the behavior of the built-in Bold button on the Formatting toolbar.

The selection change event in Excel is just about the easiest to understand. It fires only when the range of selected cells on the active worksheet changes. In most cases, the range contains only one cell. However, the selection change event is slightly limited in Excel, given that there's no way you can determine if the selection has changed from a cell to a shape, a chart, or any other object that floats on top of the cells in a worksheet.

• Changing the active cell. See the Name Box drop-down list at the top left of the Excel worksheet window (just below the toolbars).
• Using the black square at the bottom right of the range to expand or contract the size of the active range.

• Changing the selection from a cell to a shape. The SheetSelectionChange event fires only when the range of cells in the selection changes, as previously explained.
• Changing the selection from a shape back to a cell. This doesn't cause the event to trigger if you select the same cell you chose before selecting the shape. If the selected cell is different, the event triggers.
• Switching from sheet to sheet or window to window. You have to trap the SheetActivate or the WindowActivate events and then determine the selection in those events (or call the selection change event explicitly from the other event handlers). Even though the Name Box drop-down list is changing, it's constant for each sheet.

Excel has two events that allow the developer to determine if the selection change has happened and what content was changed, and it also has an event to determine if the active sheet has changed. The events are SheetSelection-Change, WindowActivate, and SheetActivate. The section "The Window Activate and Deactivate Events" later in this chapter describes how to use all three to update command bar customizations based on the current selection.

Before completing the following steps, complete the steps in the section "Update Your Controls Based on a Selection Change."

1. In the Visual Basic Editor started from Excel, double-click the Class1 project item in the Project Explorer to make it the active window.
2. Click App from the Object drop-down list and then select SheetSelection-Change from the Procedures drop-down list in the class module. In the SheetSelectionChange event procedure, add the following code so that the procedure appears as follows:

Private Sub App_SheetSelectionChange( _ ByVal Sh As Object, ByVal Target As Range) If Target.Font.Bold = True Then SetButtonState msoButtonDown, True Else SetButtonState msoButtonUp, True End If End Sub

In Excel, the second argument of the SheetSelectionChange event procedure is always a Range object. The selection change event in Excel fires only when you change the range of cells selected. The second argument in the SheetSelectionChange event procedure is a range of selected cells. If all of the cells contain text that is all bold, SetButtonState is called to set all instances of the custom Bold button to enabled and depressed.

3. Double-click the standard code module project item, Module1, in the Project Explorer to make it the active window. Place the cursor in the procedure InitEvents and press F5 to run the project.
4. Switch back to Excel, add some text to a cell, add shapes, and format the text in any cell that contains text to bold.
5. Click the workbook's content such as the bold text, text without bold formatting, or a shape.

As you click around the content, the custom Bold button on the Standard toolbar, as well as any other place you copied the button, should toggle between the up and down (depressed) state. The state of the custom Bold button should parallel the behavior of the built-in Bold button on the Formatting toolbar.

In PowerPoint, the selection can consist of text, shapes, slides, or nothing. When you change the selection from any of these types or extend or reduce the selection of one type, the selection change event triggers. One of the main differences between PowerPoint and Word's WindowSelectionChange event is that the event in PowerPoint is triggered every time you type a new character. This functionality can enable scenarios where you want to track the text that you enter. If you find a particular word, you can add "AutoCorrect" type functionality.

• Selecting text in Outline view with the mouse pointer or the arrow keys.
• Holding down the SHIFT key or the CTRL key and pressing the arrow keys to extend the text selection.
• Typing text or pressing ENTER when in text. Unlike Word, the selection change event fires when you type text.
• Switching slides in Normal view using the vertical scroll bar. Because the WindowSelectionChange event fires when you switch between slides, you can use the event to determine when the active slide has changed.
• Inserting a new slide in Normal view. This is a direct result of the previous bulleted item.
• Switching views.
• Clicking a slide icon in Outline view.
• Moving a slide icon to another position in Outline view.
• Clicking in any inactive pane in Normal view.
• Selecting multiple shapes on a slide.
• Letting the left-mouse button go when drag-selecting shapes or text.
• Selecting a slide image in Slide Sorter view or selecting in-between slide images.
• Selecting multiple slide images in Slide Sorter.

• Grouping or ungrouping shapes.
• Formatting text.
• Switching between presentations. To determine the selection when a presentation window is activated, you should set up the WindowActivate event procedure, as described towards the end of this chapter. In the WindowActivate event procedure, you can explicitly call the Window-SelectionChange event procedure. Here's how the WindowActivate event procedure would appear:

Private Sub App_WindowActivate( _ ByVal Pres As Presentation, _ ByVal Wn As DocumentWindow) PpApp_WindowSelectionChange Wn.Selection End Sub

This procedure is described in full and used later in this chapter in the description for the WindowActivate event.

Before completing the following steps, complete the steps in the section "Update Your Controls Based on a Selection Change."

1. In the Visual Basic Editor started from PowerPoint, double-click the Class1 project item in the Project Explorer to make it the active window.
2. Click App from the Object drop-down list and then select Window-SelectionChange from the Procedures drop-down list in the class module. In the WindowSelectionChange event procedure, add the following code so that the procedure appears as follows:

Private Sub App_WindowSelectionChange( _ ByVal Sel As Selection) Select Case Sel.Type Case ppSelectionShapes If Sel.ShapeRange.HasTextFrame Then If Sel.ShapeRange _ .TextFrame.TextRange _ .Font.Bold = msoTrue Then SetButtonState msoButtonDown, True Else SetButtonState msoButtonUp, True End If Else SetButtonState msoButtonUp, False End If Case ppSelectionText If Sel.TextRange _ .Font.Bold = msoTrue Then SetButtonState msoButtonDown, True Else SetButtonState msoButtonUp, True End If Case Else SetButtonState msoButtonUp, False End Select End Sub

One argument is passed to the WindowSelectionChange event in PowerPoint. As in Word, the argument is defined as type Selection. Using the Selection object in PowerPoint, you can determine whether the selection is text, a shape, a slide, or nothing.

The Select Case statement in the WindowSelectionChange event procedure evaluates the selection type of the Sel object passed into the event procedure. If the selection is a range of shapes, the first If…Then statement evaluates whether the shape range has a text frame. The shape range can contain any number of shapes on a slide, a notes page, or a master slide. You can query properties on the entire shape range to determine if they all share the same property setting. If none of the shapes in the shape range can contain text (specifically, the property HasTextFrame is False), the custom Bold button is disabled and its state set to "up."

If the shapes can contain text (that is, the property HasTextFrame is True), the If…Then statement contained within the first If…Then statement evaluates whether all the text across the range of selected shapes is bold. If all the text is bold, the SetButtonState procedure is called to set the custom Bold button to an enabled, depressed state. If the text is not bold, the custom Bold button is enabled, but the state is set to "up." The last If…Then statement works the same way, and is evaluated when the selection is text.

3. Double-click the standard code module project item, Module1, in the Project Explorer to make it the active window. Place the cursor in the procedure InitEvents and press F5 to run the project.
4. Switch back to PowerPoint, add some text and shapes, and format some text to bold in any shape.
5. Click the presentation's content, such as the bold text, text without bold formatting, or a shape.

As you click around the content, the custom Bold button on the Standard toolbar, as well as any other place you copied the button, should toggle between the up and down (depressed) state and between enabled and disabled. The state of the custom Bold button should parallel the behavior of the built-in Bold button on the Formatting toolbar.

In Outlook, the selection change event is set up using an Explorer object and not the Application object as in Word, Excel, and PowerPoint. When you start Outlook, the first window you see is an Explorer object. It's the window that displays the folders in your mailbox as well as the contents in each folder. The selection change event is named SelectionChange, and it's triggered when the selection in the active explorer window changes.

In Outlook, unlike in Word, Excel, or PowerPoint, the selection isn't passed into the selection change event procedure. You need to query the Selection property in Outlook to determine what items are contained in the Selection collection object. The Selection object contains the items selected in the active mailbox folder. Items can consist of mail, notes, appointments, or any other mailbox items found in Outlook.

• Selecting one or more items in your Inbox, Notes, Sent Items, Tasks, or any other folder.

• Having a file-system folder be the current folder in view.
• Changing the selection when Outlook Today is displayed.

The following steps show you how to set up a mode-less form that floats on top of the active explorer window (containing your mailbox folders and items) in Outlook. The contents of a text box in the form are updated with the mail item that's first in the collection of selected items. This behavior is exactly the same as that of Outlook's Preview Pane. You can display the Preview Pane by clicking Preview Pane on the View menu.

1. Start Outlook and display the Visual Basic Editor.
2. Insert a UserForm and from the control Toolbox, add a TextBox control and two labels. Set the properties on the controls and userform, as shown in the table below.

Control	Property	Setting
Label1	Caption	Selected item:
Label2	Name	lblItemType
Label2	Caption	(Item type)
TextBox1	Name	txtItemBody
TextBox1	Multiline	True
TextBox1	WordWrap	True
TextBox1	ScrollBars	2 - fmScrollBarsVertical
UserForm1	Name	frmPreview
UserForm1	Caption	Selected Outlook Item
UserForm1	ShowModal	False

The userform should appear as follows:



3. Click Code on the View menu or press F7 to display the userform's code module. Add the following code to the top of the module:

Dim OlEvents As New OutlookEvents Private Sub UserForm_Initialize() Set OlEvents.expWindow = ActiveExplorer End Sub Private Sub UserForm_Terminate() Set OlEvents.expWindow = Nothing End Sub

The class name "OutlookEvents" stated just after the New keyword in the declaration statement should be the name of the class module you add in the next step. If you change the name of the class added in the next step, you must also change the class name after the New keyword in the above declaration. When the userform is loaded, the Initialize event sets the Explorer object defined in the class containing the SelectionChange event (set up in the next step) to the active Explorer object. You can then use the explorer's SelectionChange event once the userform loads.

4. Insert a Class module, name it OutlookEvents, and add the following code:

Public WithEvents expWindow As Explorer Private Sub expWindow_SelectionChange() If Not frmPreview Is Nothing Then If expWindow.Selection _ .Count > 0 Then GetBodyText End If End Sub

The first If…Then block in the event procedure determines if the mode-less userform frmPreview is loaded. Because the userform is mode-less, the user may have closed the userform. The If…Then block is provided so that even if the userform is not loaded, the SelectionChange event procedure can still execute other code.

As previously mentioned, unlike in Word, Excel, and PowerPoint, no arguments are passed into the SelectionChange event procedure in Outlook. You need to use the Selection property on the Explorer window object to determine if any items are selected. If the count of items in the Selection collection object is greater than zero, the GetBodyText procedure, added in the next step, is called.

5. Below the SelectionChange procedure in the class module, add the following procedure:

Sub GetBodyText() Dim oItem As Object, sItemType As String Set oItem = expWindow.Selection.Item(1) Select Case TypeName(oItem) Case "MailItem" sItemType = "Mail" Case "AppointmentItem" sItemType = "Appointment" Case "NoteItem" sItemType = "Note" Case "TaskItem" sItemType = "Task" End Select With frmPreview .txtItemBody.Text = oItem.Body .lblItemType = sItemType End With End Sub

The GetBodyText procedure retrieves the first item in the Selection collection object. As in the Preview pane in Outlook, the contents in only the first item in the selection are displayed. The Select Case block determines the item type by using the built-in TypeName function in the Visual Basic for Applications language. Once the item is known, the label lblItemType is set to the string describing the item type and the text box txtItemBody is set to the body of the selected Outlook item.

6. Double-click the frmPreview project item in the Project Explorer in the Visual Basic Editor to make the userform the active window. Press F5 to run the userform.
7. In the Outlook explorer window, select a mail item in your Inbox, switch to the Notes folder and select a note item, or display any other folder and select an item.

If the folder is a mail, appointment, note, contacts, or task folder, you'll see the contents of the body of the first selected item in the userform's text box, just as the Preview pane displays the same text.