Before DoubleClick Event Handler

When you double-click a cell on a worksheet, the default action is to place Excel in edit mode. If you want to replace this action with another one, you can write code in the BeforeDoubleClick event handler.

For example, let's say you want to turn bold formatting on and off whenever a named range is double-clicked. You can add the code in Listing 7.24 to the BeforeDoubleClick event handler of a NamedRange control called NamedRange1. When you run this code, double-click the NamedRange, and then start typing. Then the text you type is appended at the end of the text in NamedRange1. This is because the default action of placing Excel in edit mode still occurs after your code is run.

Listing 7.24. Turning bold on and off when a NamedRange is double-clicked

Private Sub NamedRange1_BeforeDoubleClick(ByVal Target As _ Microsoft.Office.Interop.Excel.Range, _ ByRef Cancel As Boolean) Handles _ NamedRange1.BeforeDoubleClick

Me.NamedRange1.Font.Bold = Not CType( _

Me.NameFirstNameCell.Font.Bold, Boolean)

End Sub

Notice that we converted the value returned from Me.NamedRange1.Font.Bold to a Boolean data type because it returns an Object. This is only necessary if you have Option Strict set to True, because the explicit conversion cannot take place. If you want to ensure that the default action does not take place after your code is run, you can add a line of code that sets the Cancel property to True, as shown in Listing 7.25. This time when you double-click the cell, Excel does not enter edit mode and the text in NamedRange1 is replaced by the text you type.

Listing 7.25. Turning bold on and off then canceling the default action

Private Sub NamedRange1_BeforeDoubleClick(ByVal Target As _ Microsoft.Office.Interop.Excel.Range, _

ByRef Cancel As Boolean) Handles _ NamedRange1.BeforeDoubleClick

Me.NamedRange1.Font.Bold = Not CType( _

Me.NameFirstNameCell.Font.Bold, Boolean) Cancel = True

End Sub

0 0

Post a comment