So my goal with this post is to show you how I am planning to structure my Task Pane controls to provide flexibility. At this point, all I really have is a plan and I'll let you know if it works out as the project continues. I started with what I think is common sense. My task pane is really three controls: TaskPaneHeader, TaskPaneFooter, and TaskPaneBody. The decision here is to separate the header and the footer so I can have persistant controls like a toolbar and a status label. The TaskPaneBody which sits in the middle is basically a container for other controls which are really the rich UI portions of the TaskPane. I setup these intial structures in the Workbook startup:

Friend WithEvents m_taskPaneHeader As TaskPaneHeader
Friend WithEvents m_taskPaneBody As TaskPaneBody
Friend WithEvents m_taskPaneFooter As TaskPaneFooter

Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        SetupTaskPane()
End Sub

Private Sub SetupTaskPane()
        With Me.Application.CommandBars("Task Pane")
            .Width = 300
            .Position = Microsoft.Office.Core.MsoBarPosition.msoBarLeft
        End With
        Globals.Sheet1.Select()

        m_taskPaneHeader = New TaskPaneHeader()
        m_taskPaneBody = New TaskPaneBody()
        m_taskPaneFooter = New TaskPaneFooter()

        m_taskPaneHeader.Dock = DockStyle.Top
        m_taskPaneFooter.Dock = DockStyle.Bottom

        Me.ActionsPane.Controls.Add(m_taskPaneHeader)
        Me.ActionsPane.Controls.Add(m_taskPaneBody)
        Me.ActionsPane.Controls.Add(m_taskPaneFooter)

End Sub

So far I only have one control that I am loading into the TaskPaneBody. The following control called "Browse" is added by default right now. So in TaskPaneBody, I have the following code:

Public Class TaskPaneBody
    Friend WithEvents m_Browse As Browse

    Private Sub TaskPaneBody_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        m_Browse = New Browse()
        Me.Controls.Add(m_Browse)

    End Sub

    Private Sub m_Browse_BrowseDate(ByVal NewDate As Date) Handles m_Browse.BrowseDate
        Globals.Sheet1.CalStartDate.Value2 = NewDate
    End Sub
End Class

This code actually has an instance of the Browse control. Eventually I will need a mechanism to hide and show the different controls that will be in this container. The Body control alsy give me a place to respond to events. The event handler that you see above is when someone changes the date in my DateTimePicker control in the Browse control, a namedrange cell gets its value set to that date. In a bit you'll see how this assignment actually changes my interface.

Now in the Browse control itself, I have two main controls. One is the DateTimePicker mentioned previously. The other is simply a label which I am currently using to display the selected cell within a large set of cells in my spreadsheet. This will be obvious in a later post as I hope to use this connectivity to allow this areas of the control to display detail information from the dataset. The code in the Browse control is as follows:

Public Class Browse

    Public Event BrowseDate(ByVal NewDate As Date)
    Private WithEvents m_EngagmentCells As NamedRange = Globals.Sheet1.EngagementCells

    Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
        RaiseEvent BrowseDate(DateTimePicker1.Value)
    End Sub

    Private Sub m_EngagmentCells_SelectionChange(ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles m_EngagmentCells.SelectionChange
        Me.Label2.Text = Target.Address.ToString()
    End Sub
End Class

So you can see here that I have an event that is raised as the DateTimePicker is used. I also have my own hander here when someone moves around within my Engagement Cells namedrange. This range is the intersection of my root values in the first column and the dates across the top. Here is the promised pic:

Screen Shot

The only other interesting this here, is to mention that the DateTimePicker is only setting the first date column. The others are only forumla based with a reference to that cell with a "+n". Make sure the columns are formatted to display as a date.