KalliMan

Hi all,

I'm writting a simple Excell (v2003) Template, using VSTO 2005 (C#). And I need an event/notification if a Named Range/Defined Names is modified/deleted(from the Excell Menu, or from somewhere else). I'm not talking about the NamedRange control. I'm talking about the all of the Defined Names which are contained in the "Names" collection of the "Application" object.

Any suggestions are welcome.

Regards:

KalliMan.



Re: Visual Studio Tools for Office Excel Named Ranges events

Dennis Wallentin

Hi Kalliman,

If You're looking for a way to prevent end users from deleting names then we can hide them as the following snippet VBA code shows (sorry at present I'm not sitting with my dev machine):

Code Snippet

Dim wbBook As Workbook
Dim myNames As Name

Set wbBook = ActiveWorkbook

For Each myNames In wbBook.Names
myNames.Visible = False
Next

Except for that it does not exist any event to track any changes of names.






Re: Visual Studio Tools for Office Excel Named Ranges events

KalliMan

Hi Dennis,

Thank you for your replay, but unfortunately I'm not looking for that Sad. Either more this code wont work if the user adds Name after this code is called ...

My scenario is the following ...

I have some data stored on the Excel Sheets and random part of this data (User Choose that via Excel Input box) is "dynamic binded" to some external data source (DB as an example). User is able to choose some range or may choose a Defined Name. So it is important for me to handle when the range of the Defined Names has been changed or Defined Name is deleted.

One possible solution is to wrap the "Name" object with the "NamedRange" control. But to do that I need to know when the user Adds or Deletes New Ranges.

Well ... I'm not sure if I'm clean enought, but at all I believe that there is no way to behave the Events Receiving ...

Best Regards:

KalliMan





Re: Visual Studio Tools for Office Excel Named Ranges events

Dennis Wallentin

Hi KalliMan,

I suspected it was a "too easy" answer for the issue. As long as we cannot trap when user edit/delete names we need to consider a different approach.

One simple approach is to add a standard worksheet in which You create the structure of initial rangenames and their references. Next, You add a procedure that maintain the list, i e iterate through the name collection and add/delete/update the names/references.

The key question is when this procedure should be executed. One "trigger" is to use the procedures that retrieve data from various external sources, ie call the procedure before executing anything else . Another "trigger" is to use either the event of Worksheet change or selection change. All in all, workarounds always come with a price...






Re: Visual Studio Tools for Office Excel Named Ranges events

KalliMan

Hi Dennis,

There is no problem for me to update the Worksheet and the Ranged Names when the external source has been updated. The problem is how to update the external source when user updates the Names.

Anyway ... It seems that my solution will be hardcoding. Sad. After 2 days of researching I haven't found any possible event rised when the user modifies the Range of the Defined Names, so I'll try to workaround this.

Best Regards:

KalliMan