How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

How to Capture right click Delete/Insert on Excel
 

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

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

How to Capture right click Delete/Insert on Excel
 

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.

Yes you are right, i looking to trap the event AFTER the user selected
"Entire Row" and THEN clicked "Ok" button.

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


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

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