Accessing the Actions Pane from a Document

To determine which text and formatting should appear in the label on the actions pane, write code in the SelectionChange event handler of Sheet1. When the selection in the worksheet changes, apply the formatting of the ActiveCell to the text (the style name) in the label on the actions pane. You can get access to the label by using the Globals class.

1. Add the code in Listing 5.8 to the SelectionChange event handler of Sheet1.

Listing 5.8. The SelectionChange event handler of Sheet 1

Private Sub Sheet1_SelectionChange(ByVal Target As _ Microsoft.Office.Interop.Excel.Range) Handles _ Me.SelectionChange

' Set a variable to the label on the actions pane control. Dim Actions As System.Windows.Forms.Label

Actions = Globals.ThisWorkbook.ActionsControl.SelectionFont

' Retrieve font information from currently selected cell. Dim FontName As String = Application.ActiveCell.Font.Name Dim FontSize As Short = Application.ActiveCell.Font.Size Dim FontBold As Boolean = Application.ActiveCell.Font.Bold Dim FontItalic As Boolean = _

Application.ActiveCell.Font.Italic Dim FontAttribute As FontStyle = FontStyle.Regular Dim ExcelStyle As Excel.Style = Application.ActiveCell.Style Dim StyleName As String = ExcelStyle.Name

' Apply Bold, Italic, and Underline styles to label if they ' are applied to the active cell. If FontBold = True Then

FontAttribute = FontStyle.Bold End If

If FontItalic = True Then

FontAttribute = FontStyle.Italic End If

If Application.ActiveCell.Font.Underline = _

Excel.XlUnderlineStyle.xlUnderlineStyleSingle Then

FontAttribute = FontStyle.Underline End If

' Add the Style name and formatting of the active cell to the ' label on the actions pane. Actions.Text = StyleName

Actions.Font = New Font(FontName, FontSize, FontAttribute)

2. Press F5 to run the code.

When you run this code, the custom control you designed is displayed on the actions pane as soon as the document opens. When you move your cursor into a cell, the style name of the currently selected cell (the active cell) is displayed in the label on the actions pane with the formatting from the active cell applied, as shown in Figure 5.5.

Excel Vba Dual Pane Form
Figure 5.5. Displaying the formatting of text in a cell on the actions pane
0 0

Post a comment