How to Capture right click Delete/Insert on Excel

[Gummadi]

Hi,

I wanted to know how do we capture the right click Delete/Insert event in
Excel.

This is not same as where we select a row or column and then right click and
select Insert/Delete. I am able to capture that event.

I wanted to know about the event when we right click on a cell, and the
select Delete/Insert and then Select option "Entire Row" or "Entire Column".
This is what i want to capture.

Can someone please help me with this?

Thanks,

Gummadi

 

[Gary''s Student]

If you want to trap the right-click, then use this event macro in the
worksheet code area:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox ("A right-click is better than a wrong click!")
End Sub

If you then wanted to trap a resulting column/row insert/delete, then a
different event macro could be used.

 

[Peter T]

Add a class module named Class1

''''' code in a normal module

Dim cCellDel As Class1

Sub SetUp()

Set cCellDel = New Class1
Set cCellDel.btn = CommandBars("Cell").FindControl(ID:=292) '

End Sub

Sub CheckDelRow()
Dim s As String

If cCellDel.RowIsDel(s) Then
' handle deleted
End If

End Sub

'''' code in class named Class1
Public WithEvents btn As CommandBarButton
Private mrCurRow As Range
Private msCurRowAddr As String

Private Sub btn_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
Set mrCurRow = Selection.EntireRow
msCurRowAddr = mrCurRow.Address
Application.OnTime Now, "CheckDelRow"
End Sub
Public Function RowIsDel(Optional sRowAddr As String) As Boolean

On Error Resume Next
sRowAddr = mrCurRow.Address
If Not mrCurRow Is Nothing And Err.Number Then
MsgBox msCurRowAddr & " deleted"
RowIsDel = True
On Error Resume Next
sRowAddr = msCurRowAddr
End If
Set mrCurRow = Nothing
msCurRowAddr = ""

End Function

Run Setup then try rt-click Cell delete rows

Regards,
Peter T

 

[Gummadi]

Hi Peter,

This event fires when the user does a right click on the sheet and selects
Delete. But then after that a popup comes which shows 4 options to the user.

1.Shift Cells Right
2. Shift Cells Down
3. Entire Row
4. Entire Column

So if the user does not select Entire Row here, that i cannot actually
execute the code that you have given.

I wanted to know if there is way to capture the event after he selects
"Entire Row".

Thanks,
Shailendra

 

[Peter T]

The code I gave you in effect traps the action of user selecting delete from
the rt-click cell menu, selecting Entire Row, then clicking OK to complete
the action. The example responded to that sequence, which is what I thought
you wanted, and additionally reported the address of the newly deleted rows.

I'm not sure what use it would be to you to "capture the event after he
selects Entire Row" (but before pressing OK). That is only selecting an
option, user might change mind and select some other option or simply press
cancel. Do you mean you want to intercept the OK click before the action is
completed (say to do the equivalent of Cancel=True and abort), not
straightforward.

In theory you could trap events in that little dialog, though I don't think
that would be possible (yet alone safe) in VBA as it would entail hooking
into the dialog window and its controls at a time when normal VBA is
suspended.

Regards,
Peter T

 

[Gummadi]

I am sorry that i forgot to mention ... i am using Excel 2007 with VSTO.
Yes you are right, i looking to trap the event after the user selected
"Entire Row" and then clicked "Ok" button.

 

[Peter T]

The VBA code I gave you should do exactly what you say you want, in all
versions from Excel 2000 - Excel 2007.

In VSTO you would of course have to adapt the VBA to whichever language you
are using.

Did you actually try the code in VBA.

Regards,
Peter T

 

[Gummadi]

I have tried the code you have posted, but the event fires once i click
delete. The problem with this is that, the user might not actually decide to
delete the row once the popup with 4 options comes. What is happening with
your code is that once the user selects a cell, right clicks and selects
delete, the event is fired and the row gets deleted. I wanted to handle the
event after the user selects the option "Entire Row", because that is when he
has actually deleted the row.
Please let me know if i have misunderstood your explanation.

 

[Peter T]

Maybe I am misunderstanding what you want, the difficulty I am having is
what I understand you are asking seems to change

The code does exactly what you stated in your last but one post.

IOW, it traps the event after user has pressed the OK button with the delete
Entire Row option selected.

If that's not what you want, for example you want to intercept the OK click
before the action is processed, refer to the explanation I gave in my second
post in this thread.

The click event is handled even if user cancels. However the code caters for
that -

If Not mrCurRow Is Nothing And Err.Number Then

IOW, it's the error that confirms user has deleted rows because the original
trapped range no longer exists.

No doubt in whatever language you are using in VSTO you will need examine
the equivalent error in a different way.

Regards,
Peter T