VBA/VB: Create Excel Events & Disable Right-Click

[RobDog888]

This code can be converted for use in VB, but heres the VBA version.

Requirements: Excel 97 +
Security: Enable Macros

With the ability to create and attach to Excel events you can take
almost complete control over Excel. The same logic can be used
for the other Office products.

In this demo I create the Close event to allow the programmer to
take control of the event. I also disable the right-click popup
menu in the SheetBeforeRightClick event.

'ThisWorkBook
Option Explicit
 
Private RD As clsMyEvents
 
Private Sub Workbook_Open()
    Set RD = New clsMyEvents
    MsgBox "My Events Created!", vbOKOnly + vbInformation, "My Excel Events"
End Sub
 
 
'Name: clsMyEvents
'Instancing: Private
Option Explicit
 
Public goXL As Excel.Application
 
Public WithEvents MyEvents As Excel.Workbook
 
Private Sub Class_Initialize()
    Set goXL = GetObject(, "Excel.Application")
    Set MyEvents = goXL.ActiveWorkbook
End Sub
 
Private Sub Class_Terminate()
    Set goXL = Nothing
End Sub
 
Private Sub MyEvents_BeforeClose(Cancel As Boolean)
    Dim iResp As Integer
    iResp = MsgBox("RobDog888: Do you want to save your workbook?", vbYesNoCancel + vbQuestion, "My Excel Events")
    If iResp = vbYes Then
        goXL.ActiveWorkbook.Save
        If goXL.ActiveWorkbook.Saved = True Then
            MsgBox "Saved!", vbOKOnly + vbInformation, "My Excel Events"
        Else
            MsgBox "Error Saving Workbook!" & vbNewLine & "Sorry!", vbOKOnly + vbCritical, "My Excel Events"
        End If
    ElseIf iResp = vbCancel Then
        Cancel = True
    End If
End Sub
 
Private Sub MyEvents_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "No Right-Clicking!", vbOKOnly + vbInformation, "My Excel Events"
    Cancel = True
End Sub

Edit: I added two workbooks. One for 97-2003 and the other for 2007.