Chapter 18. Detail Windows and Task Panes

Chapter 18. Detail Windows and Task Panes

We may use another technique to show details in windows and task panes using the SelectionChange handlers.

Detail Windows

Let's switch to the payments-configuration workbook, select the EventHandlers worksheet and add the data:

SelectionChange handlers used to create detailed views

The HANDLER_CODE of the Company Payments handler contains the code:

SELECT
    p.[Date]
    , p.[Sum]
    , c.Name AS Company
    , i.Name AS Item
    , p.Comment
FROM
    dbo67.Payments p
    LEFT OUTER JOIN dbo67.Items i ON i.ID = p.ItemID
    INNER JOIN dbo67.Companies c ON c.ID = p.CompanyID
WHERE
    p.CompanyID = @CompanyID

The HANDLER_CODE of the Item Payments handler contains the code:

SELECT
    p.[Date]
    , p.[Sum]
    , c.Name AS Company
    , i.Name AS Item
    , p.Comment
FROM
    dbo67.Payments p
    INNER JOIN dbo67.Items i ON i.ID = p.ItemID
    LEFT OUTER JOIN dbo67.Companies c ON c.ID = p.CompanyID
WHERE
    p.ItemID = @ItemID

As we may suppose, the add-in executes the specified code on the SelectionChange event.

The handlers use @CompanyID and @ItemID parameters accordingly to filter output data.

 

Let's switch to the payments workbook, select the Reports worksheet, and select the Payments (view) view.

If the view is already activated, click Reload, Reload Data and Configuration.

Now, when we change a row, the add-in launches windows like this:

Sample of the Excel task pane with details by a company Sample of the Excel task pane with details by an item

We may select another row. Windows will stay on top and show related information.

You may click on the window status line to see the executed SQL command like this:

SQL query used to show details in a Microsoft Excel task pane

 

In this example, we have used the direct SQL codes stored in the EventHandlers table.

This is useful when you do not want to modify your database.

Otherwise, you may use stored procedures with parameters.

Cell Editor

The SaveToDB add-in adds great support for editing codes in cells.

When the cell contains a multiline value, the add-in launches the Cell Editor like this:

Cell Editor sample

You may edit the text in the editor and click the Save button to update the underlying cell.

You may turn on/off the editor using the Options, Show Cell Editor Task Pane option.

Task Panes

Use of detail windows is a good solution when you need windows that are always visible.

If you need context windows, you may use task panes.

Let's add the following handlers to the EventHandlers table for the uspPayments procedure:

SelectionChange handlers of the uspPayments procedure

I have copied the view rows, changed the view name, and added _TaskPane to the TARGET_WORKSHEET field.

The HANDLER_CODE codes remain the same.

 

Let's switch to the payments workbook, and change the query to the Payments (sp) procedure.

When we change a row, the add-in shows task panes like this:

Sample of the Excel task pane with details by company payments Sample of the Excel task pane with details by item payments

Users may customize column formats:

Sample of customing column formats in the Excel task pane

Also, users may dock task panes and turn them on/off using the Options, Show Task Panes option.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK