How to create Excel HostControl from code behind Custom Control on TaskPane?

In VSTO Excel document level application I need to add NamedRange controls on various worksheets. All examples I found show the creation of NamedRange from Worksheet_startup event like this:

Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
textInCell = Me.Controls.AddNamedRange(Me.Range("A1"), "cellText")

But I cannot seem to be able to create a named range control on a sheet from any other event but that Sheet_startup.

I want to create a named control on a sheet from my code on a custom control on a taskpane. If this is possible, could someone please point me to a code example

Thank you,
Marat.

You can create an instance of your custom control, add it to the Actions Pane, then subscribe to some event in your custom control (i.e. Button.Click). The event handler can then directly add the control to the worksheet (if the worksheet is the owner of the event handler) or add the control to the worksheet by first retrieving the worksheet from the Globals class (i.e. Globals.Sheet1).

-Phil

Phil,

Thank you, but that does not answer my question.

I want to initiate <sheet object>.AddNamedRange method from the code behind the control, not from the sheet's own method or sheet_startup event.

From the workbook startup event, going through the collection of worksheets, I add to the taskpane one control for each sheet, passing the sheet name as parameter to the control's constructor.

Each control gets a hold of it's assigned worksheet and creates Host Controls on this worksheet based on the analysis done by private method inside the custom control itself.

From the control's constructor I am trying to get to the Globals.ThisWorkbook.sheets("sheet1") object by name, but for some reason I cannot get that object.

How can I get to that Globals.ThisWorkbook.sheets("sheet1") object from inside my control's scope, so I could call the .AddNamedRange from it

Thank you for your reply,

Marat.

>> Why not pass the sheet object, rather than the name

Exactly so. That is what I ended up doing as a work-around instead of what I wanted to do at first. It works. I pass the sheet object itself to the constructor instead of only the name, as I wanted. Therefore, I researched the surroundings a little while trying to make it work.

When I started to trace through the code in the debugger, trying from the code behind different objects (workbook, sheet, control) to point my Dim'ed objects to Globals members (like a worksheet), it seems that I cannot see into the "outside world" from within the control.

Almost seems like a domain crossing protection is in place. I could pass the sheet object into custom control from a Sheet event and then use the Sheet object's abilities from inside the custom control, but I cannot snoop for the sheet objects from inside that same custom control and use the capabilities of Sheet object without the "outside world" (in this case the code in a sheet or workbook scope) offering me the trust explicitly by providing the object itself, and not less! Knowing the Sheet object's name was not enough. I could not do "For each" on the sheets collection and grab those objects from the "Globals" either, while inside the custom control.

Although I could understand such logic, but why is there no documentation to this fact that I could find

Perhaps this is some fundamental rule that I missed somehow There is a difference between a button_event accessing the form's properties and a custom control's methods modifying the parent object's environment and behaviour. A kind of "don't you touch my objects unless I explicitly give one to you by myself".

From the answers of those who know so far I concluded the following:

In Excel VSTO project, a custom control on a task pane cannot instantiate a sheet HostControl object without being passed the sheet object itself into the custom control constuctor as a parameter object.

Is this true

Thank you,

Marat.

Of course I can always find an alternative (already did), like in this case, passing the object into the constructor. I could pass the whole woorkbook to the constructor too, I suppose, just in case, and access anything I need from there. But that is a different tactic, though, not the one I wanted to use originaly.

It's not a problem for me iether, rather a healthy desire to know why something doesn't work the way I expected it to work. I wanted the control to independently arrange things in any place on the workbook the control decides to. But that tactic was not successful. The next time I will not consider it as a possible tactic.

About my specific question. Although I did not get a straight "yes" or "no" answer, I concluded that the statement below is a true statement:

>> In Excel VSTO (document level) project, a custom control on a task pane cannot instantiate a sheet HostControl object without being passed the sheet object itself into the custom control constuctor as a parameter object.

Thank you for your help.

Marat.

The answer to your specific question is not strictly true. There are any number of ways for a custom control to obtain a reference to a particular worksheet without passing it to the control's constructor. At some point, however, you need a reference to the worksheet in order to call the method that adds a control. The exact method employed is a matter of good design principles, convenience, and personal taste.

The Globals class is just a simple means of accessing the known Worksheet/Workbook instances from anywhere within the project. It would be nice, I admit, if the VSTO Workbook class exposed a collection of VSTO Worksheet instances rather than just the underlying Excel worksheets. You could (as Cindy suggested) expose your own worksheet collection from the Globals class that allowed indexing by name (as shown in the example below). However, keep in mind that relying on the Worksheet.Name property can be problematic as the user can change it on a whim. To me, it seems better to simply pass each control the worksheet(s) on which it is to operate or to associate a unique identifier with each worksheet that you can then use as a map to the correct worksheet.

-Phil

That answers it!

Thank you Phil and Cindy.

Marat.