access control on new excel sheet

[Alisa]

I am using VSTO to design a excel 2003 workbook. I am using an action pane to login in my user and then it populates the worksheet based on the user’s access. The goal of the workbook is dynamically add in the projects for each user. Then summarizes all of the projects information on a one sheet (i.e. totals and etc.)
Here is where I am at:
Logins in a users, populates the projects that user has access too.
It creates, names based on the project then auto fills the sheets based on the user access.

Here is how I create and add a sheet:


Dim newSheet As New Microsoft.Office.Interop.Excel.Worksheet
newSheet = CType(Globals.ThisWorkbook.Worksheets.Add, Excel.Worksheet)


Being I do not know how much data is returned on each project…. I am finding it difficult to create formula for any columns worth of data.
So I thought about using the list objects. But I am unable to access the controls to the newly created sheets.

I have looked at this project http://msdn.microsoft.com/office/understanding/vsto/codesamples/default.aspx?pull=/library/en-us/odc_vsto2005_ta/html/officevstodynamiccontrols.asp

I found this bit of code in the project and I am not sure it what I need to access the controls on the newly added sheet. Do I plan that in this workbook code?

' Gets the VSTO 2005 worksheet view for a worksheet.
' The Worksheet view is used to dynamically add controls to a worksheet.
Private Function GetExtendedWorksheet(ByRef nativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet) As Microsoft.Office.Tools.Excel.Worksheet

' Get the IHostItemProvider instance.
Dim hostItemProvider As Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider = _
CType(RuntimeCallback.GetService(GetType(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)), _
Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)

' Create the new worksheet and return it to calling function.
Return New Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider, _
RuntimeCallback, _
nativeWorksheet.CodeName, _
Container, _
nativeWorksheet.Name)
End Function


Also I that project all of the code lives in the sheet code area. All of my code is in a separate class.

I think I covered all the points….

????