VSTO Task Pane Structure
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:
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.