Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing excel insert/delete events.
How can we capture excel insert/delete column/row event?
One way is to use "Sheet_change" event, but how will come to know, what change caused this event to raise? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing excel insert/delete events.
Hello Gaurav,
The following demonstration code example should point you in the right direction for identifying if rows or columns are inserted/deleted. Select a cell in Row 1 out of the way of your data and to the right of any column likely to be used in your application. Define a name for the cell and call it ColTest. (See Define names in Help if required) Identify the column number of the cell and enter that number in the cell. (Easy way is to insert =Column() then Copy - Paste Special - Values over top of itself. DO NOT leave as a formula.) Select a cell in column A out of the way of your data and below any row likely to be used in your application. Define a name for the cell and call it RowTest. Enter the row number in the cell. (For the above do not select the far right column or the last row on the worksheet or you will not be able to insert any columns or rows because Excel will not allow cells with data to be pushed off the side or bottom of the worksheet.) Copy the below Worksheet_Change code into the VBA editor. (I assume you know how to do this because you have already mentioned using Change Event.) Note my comments in the code. You need to understand that if using UsedRange anywhere in your code then the above cells will be included and you may have to handle that situation. I suggest that you do not use cells too far away to the right or below your data. If using UsedRange in other code think about Resize and reduce it by one column and one row. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Range("ColTest").Column = _ Range("ColTest").Value Then 'MsgBox only for demo. Normally no code 'required if no Insert/Delete 'MsgBox "No column inserted or deleted" GoTo TestRows End If If Range("ColTest").Column _ Range("ColTest").Value Then 'Insert you code here in lieu 'of the MsgBox MsgBox Range("ColTest") _ .Column - Range("ColTest") _ .Value & " Column/s inserted" GoTo TestRows End If If Range("ColTest").Column < _ Range("ColTest").Value Then 'Insert you code here in lieu 'of the MsgBox MsgBox Range("ColTest") _ .Value - Range("ColTest") _ .Column & " Column/s deleted" GoTo TestRows End If TestRows: If Range("RowTest").Row = _ Range("RowTest").Value Then 'MsgBox only for demo. Normally no code 'required if no Insert/Delete 'MsgBox "No Row inserted or deleted" GoTo ReEnableEvents End If If Range("RowTest").Row _ Range("RowTest").Value Then 'Insert you code here in lieu 'of the MsgBox MsgBox Range("RowTest") _ .Row - Range("RowTest") _ .Value & " Row/s inserted" GoTo ReEnableEvents End If If Range("RowTest").Row < _ Range("RowTest").Value Then 'Insert you code here in lieu 'of the MsgBox MsgBox Range("RowTest") _ .Value - Range("RowTest") _ .Row & " Row/s deleted" GoTo ReEnableEvents End If ReEnableEvents: Range("ColTest") = Range("ColTest").Column Range("RowTest") = Range("RowTest").Row Application.EnableEvents = True End Sub Because you are disabling events, if your code should fail during testing then events remain turned off until you restart Excel so insert the forllowing code into the VBA editor and run it if the code fails for any reason. For convenience you can place it below the above code in the worksheet module and you can run it from within the VBA editor. (You won't see anything when it runs but it does turn the events back on) Sub ReEnableEvents() Application.EnableEvents = True End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing Insert/Delete Events in Excel Sheet using c# | Excel Worksheet Functions | |||
Excel 2007 - Cannot Delete or Insert Rows | Excel Discussion (Misc queries) | |||
How to program excel to insert / delete any worksheet? | Excel Worksheet Functions | |||
Capturing excel screenshots | Excel Discussion (Misc queries) | |||
How to Track Column Insert/Delete Event in Excel | New Users to Excel |