Overriding events of excel sheet using VBA

Question

Hi, I need to programmatically override the following events of a worksheet:

• BeforeDoubleClick
• SelectionChange
• BeforeRightClick

I have been able to override the OnActivate event using the following code:

sheet.OnSheetActivate = "MyOwn_Activate"


Private Sub MyOwn_Activate()

    myForm.Show

End Sub

I have implemented BeforeDoubleClick on similar lines:

sheet.OnDoubleClick = "My_BeforeDoubleClick"

Private Sub My_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
...
End Sub

However, an 'argument not optional' error is thrown at run-time when user double clicks a cell on the sheet. Can someone please suggest how should I pass the paramters?

In addition, I am not able to find event names for SelectionChange & BeforeRightClick. I tried:

sheet.BeforeRightClick = "My_BeforeRightClick"
sheet.SelectionChange = "My_SelectionChange"

But, both the above lines do not work.

Any help/ suggestion is greatly appreciated.

Thanks :)

Answer 1

That is not possible.

You can tie the method this way, only for 3 events (OnSave, OnSheetActivate, OnSheetDeactivate). Please refer: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook_members%28v=VS.80%29.aspx

EDIT

Dim beforeRightClick As String

Public Sub newBeforeRightC(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address
    beforeRightClick = "newTwoBeforeRightC"
    Cancel = True
End Sub

Public Sub newTwoBeforeRightC(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address & "-" & 2
    Cancel = True
End Sub

Private Sub Worksheet_Activate()
    beforeRightClick = "newBeforeRightC"
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    CallByName Me, beforeRightClick, VbMethod, Target, Cancel
End Sub

This code is part of the Sheet1 (i.e instance of a workbook class) inside VBA.

Answer 2

It can be done, by targeting the event at a worksheet level, inserting your own code and then cancelling the original event.

This code needs to go into the Sheet Object in the Project Explorer area for the worksheet that you are targeting.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

MsgBox "Double click"
''//Your override code

''//Continue with Double click action
''//OR optionally cancel the double click event

Cancel = True

End Sub

The other two event that you would want to target are:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

these can be used in the same place.