（转）Excel中如何捕捉删除行操作

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

監控刪除行及列

原文来自： http://cat14051.mysinablog.com/index.php?op=ViewArticle&articleId=43207   

'// Worksheet RowColumn Deleted Event

'// This is NOT a real event but just hack the command button.

'// You can know when the rows or the columns was deleted by user's opelation

...

'

' 執行監控程序

'

' Module

Option Explicit

---

Sub EventHack() ' 執行監控程序

AssignMacro "JudgeRng"

End Sub

---

Sub EventReset() ' 取消監控程序

AssignMacro ""

End Sub

---

Private Sub AssignMacro(ByVal strProc As String)

Dim lngId As Long

Dim CtrlCbc As CommandBarControl

Dim CtrlCbcRet As CommandBarControls

Dim arrIdNum As Variant

'// 293=Delete menu of the right click on row

'// 294=Delete menu of the right click on column

'// 293=Delete menu of the Edit of main menu

arrIdNum = Array(293, 294, 478)

For lngId = LBound(arrIdNum) To UBound(arrIdNum)

Set CtrlCbcRet = CommandBars.FindControls(ID:=arrIdNum(lngId))

For Each CtrlCbc In CtrlCbcRet

CtrlCbc.OnAction = strProc

Next

Set CtrlCbcRet = Nothing

Next

End Sub

---

Private Sub JudgeRng()

If Not TypeOf Selection Is Range Then Exit Sub

With Selection

If .Address = .EntireRow.Address Then

Call DelExecute("Row:" & .Row, xlUp)

ElseIf .Address = .EntireColumn.Address Then

Call DelExecute("Column:" & .Column, xlToLeft)

Else

Application.Dialogs(xlDialogEditDelete).Show

End If

End With

End Sub

---

Private Sub DelExecute(ByVal str, ByVal lngDerec As Long)

MsgBox "deleted:" & str ' call any program

Selection.Delete lngDerec

End Sub

'

...

crdotlin: 有時候我們要對儲存格個刪除或整欄的刪除或整列的刪除設限，需要監視刪除的動作。這是大家常遇到的問題。本程式使用一個類模組在當刪除了儲存格或整欄或整列時，分別產生CellDelete、ColDelete或RowDelete事件，您可依需要做適當的處置。本文的處理是在整列遭刪除時提示輸入密碼，密碼錯誤則使用Application.Undo復原該列。

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>