Custom Delete Event

Submitted by Masaru Kaji. Excel doesn't provide an event for capturing the deletion of a row or column. The following code is an "Event Hack": By replacing the built-in deletion choices, it provides the programmer control over what the user can delete. In the sample provided, a message box appears, informing the user of what has been deleted:

Sub EventHack()

AssignMacro "JudgeRng" End Sub

Sub EventReset()

AssignMacro "" End Sub

Private Sub AssignMacro(ByVal strProc As String) Dim lngld As Long Dim CtrlCbc As CommandBarControl Dim CtrlCbcRet As CommandBarControls Dim arrldNum 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 arrldNum = Array(293, 294, 478)

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

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 Selection.Delete lngDerec End Sub

Selecting with SpecialCells

Submitted by Ivan F. Moala.

Typically, when you want to find certain values, text, or formulas in a range, the range is selected and each cell is tested. The following example shows how SpecialCells can be used to select only the desired cells. Having fewer cells to check will speed up your code:

0 0

Post a comment