KalliMan

Hi all,

I'm writting a simple Excell (v2003) Template, using VSTO 2005 (C#). My question is How can I determinate when the user Inserts or Deletes Row(s) or Column(s) in the sheet table. There is only "Change" event of a Sheet object whcih is raised for all changes. But I'm unable to determinate which of changes are affected only from Inserting/Deleting of Row(s)/Column(s).

Regards:

KalliMan



Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

Cindy Meister

See if the discussion in this message thread addresses the scenario you have in mind

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1437554&SiteID=1

The short answer is: there's nothing built into Excel that will let you do this easily.






Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

Dennis Wallentin

Kalliman

See if You can use the Workbook_Calculate or Workbook_SheetCalculate (both of them a triggered when deleting/inserting row/columns. Since we usually want to track a limited area of worksheet You may consider to use a named columns / rows and check if it changes or not (address). Of course, there need to be some calculation that is made in the targeting sheet.






Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

KalliMan

Thanks guys for your suggestions,

Dennis are you sure that you mean "Calculate" event My Excel doesn't trigger the "Calculate" event when deleting/inserting row/columns.

Anyway... As I understood there is no simple way. Hope that there is some way to do that Smile. I have some (may be crazy) ideas to hook the excel Menus. I'm not sure is that possible, but I'll try this.

May be you will ask me a question "Why ... do you want to handle the proccess of deleting / inserting" Well the answer is complicated. My app is binded with UI (dataGrdiView on the task pane). On each user action I have to do something. I.E. if the user deletes columns I have to Alter my DataGridView on my UI. If the user adds column I have to add this column in to my DGV.

Any ideas are wellcome.

Regards:

KalliMan





Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

Dennis Wallentin

HI Kalliman,

1. Open Excel

2. Add in cell A1: =1*1 in Sheet 1

3. Open the VB Editor and add the following event to the ThisWorkbook module:

Code Snippet

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "I'm here!"
End Sub

4. Add the following event to Sheet1 module:

Code Snippet

Private Sub Worksheet_Calculate()
MsgBox "I'm here too!"
End Sub


Works well in both Excel 2003 and Excel 2007






Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

KalliMan

Hi Dennis,

Ok, but i may don't have any formulas in my sheet...

Regards:

KalliMan





Re: Visual Studio Tools for Office Notification when Iserting/Deleting Excel Rows/Columns.

Dennis Wallentin

Hi again,

In view of the solution You work on it looks like the insert/delete rows/columns "event" is a critical part.

When we take that into consideration I believe You need to go via the route I suggest. You can create a name that include a calculation (=1*1) and add the name during runtime to a cell, like =Test. However, if You don't think it's not doable then it's no longer an option.

Since You use a DataGridView (or similar control) to show the users preferences You can perhaps consider the following:

1. Define a name for the initially actual range (predefined or through user's selection)

2. Whenever users insert / delete rows/columns within the defined range the name will always reflect the updated range and

3. "Constantly" update the GridView by refering to the named range as a source and refresh it.