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

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.
--
Gary''s Student - gsnu200830


"Gummadi" wrote:

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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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