This has been posted in the newsgroups a few times, but really is required when adding dynamic controls. If you are trying to get references to the managed (Excel.Tools) object, this is the way to go. This creates a new managed worksheet object that has a controls collection that you can then use. Here is the basic code for the function:
internal Microsoft.Office.Tools.Excel.Worksheet GetExtendedWorksheet(
ref Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet) {
// Get the IHostItemProvider instance.
Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hostItemProvider =
(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)(RuntimeCallback.GetService(typeof(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);
}
During a recent project where we are customizing Excel 2003 using Visual Studio Tools for Office 2005, we encountered two issues that we thought were our problems:
Issue 1: When you dynamically add a control to a worksheet, such as a combobox or lisbox, and then go into "formula mode" by clicking on the formula toolbar the controls do not repaint correctly. If you navigate to a worksheet other than the one they are added to, the controls will appear to "float" in their original fixed position in between sheets.
Issue 2: When you dynamically add a control to a worksheet, such as a combobox or listbox at a range that is part of a group, the controls "bitmap" image will be displayed when the group is hidden using range.ungroup.
After spending a couple of hours banging our heads and cursing the bits we were writing, we brokedown and sent an email to Eric Carter and his team. They quickly responded with "This is a bug, thank you for bringing this to our attention". We asked for a gold star so Eric kindly sent a copy of his book.