BlackBar

Is there a possibility to know (to rise an event) when user deletes a row in table How can I do something before this action
P.S.: I'm using VSTO 2005 SE. Code in C#. Thanks!



Re: Visual Studio Tools for Office Excel Events

Cindy Meister

Which version of Excel are you targeting 2003 or 2007




Re: Visual Studio Tools for Office Excel Events

BlackBar

Excel 2007!





Re: Visual Studio Tools for Office Excel Events

Steve Hansen

Hi BlackBar,

By "table", I'm assuming you mean worksheet - please advise if this is incorrect. There is no event that you can directly tap into to catch this. About the best you can do is use the SheetChange event in combination with some sort of system you rig up to figure out if an item of interest has been deleted. The practicality of this depends on how much control you have over the data on the worksheet.

Regards,

Steve






Re: Visual Studio Tools for Office Excel Events

Dennis Wallentin

Hi BB,

See if the following event can help You trap row deletion:

Private Sub Application_SheetChange(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles Application.SheetChange
Dim sheet As Excel.Worksheet = Sh
MessageBox.Show(sheet.Name & " " & Target.Row)
End Sub

Edit: Next time I will refresh the cache before postings.






Re: Visual Studio Tools for Office Excel Events

BlackBar

This event rises after the row is deleted, so I can't get data from it, for example the ID of the record which was deleted.





Re: Visual Studio Tools for Office Excel Events

Cindy Meister

<<This event rises after the row is deleted, so I can't get data from it, for example the ID of the record which was deleted.>>

When I was playing with this yesterday (before deciding to give the Excel specialists a chance at it ) I found the SelectionChange event fires first. So you could store the contents before deletion in a (static) global variable then compare after the Change event fires.






Re: Visual Studio Tools for Office Excel Events

Dennis Wallentin

BB,

Well, a true Excel specialist (and not some renegade Excel specialists ) would ask:

Why do You need to catch the process of deleting rows in the first place

No matter what, the solution will be very clunky...






Re: Visual Studio Tools for Office Excel Events

Steve Hansen

BB,

I'd second what Dennis says. If you proceed down the current path, best case you'll write a ton of code to try and make it work correctly and even if it does work, since you'd be using SheetChange (and potentially SelectionChange) your solution would make the workbook uncomfortably sluggish since you'd have a bunch of code hanging of these events that are constantly being triggered - and most of them would be false positives (so to speak). Been there, done that, not worth the effort.

Since there are so many ways that people work with data on a worksheet that needs to be synched with a database, I hesitate to give you solid guidance because it depends on your circumstances (maybe you could follow up with a post describing the data you're working with and what you'd like to do). I suppose we can all chime in with potential new approaches though. Here is one alternative.

Since you are wanting to catch row deletions, it sounds like you're displaying the data in a tabular form. Is it also safe to assume that the data in the worksheet was populated from the database If so, consider the database as the system of record and focus on always keeping the worksheet data in synch by refreshing from the database. Do not allow direct edits on the worksheet to trigger database changes. You could lock the worksheet in a manner that would prevent or significantly limit what users can do to the data in the worksheet. Then, provide a UI mechnism (directly on the sheet, task pane, ribbon, win form) for creating, updating, or deleting individual items.

If you give us more details I'm sure one of us can help you find a more reliable approach.

Regards,

Steve Hansen






Re: Visual Studio Tools for Office Excel Events

BlackBar

Hi!
Thank you all for helping. This idea was not very good. I understand it now. I will try to find a better approach. There will be a bunch of questions, I think Wink So, don't go far from here %)))