VBA/VB: Create Excel Events & Disable Right-Click-VBForums
Results 1 to 4 of 4

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

Hybrid View

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,088

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

    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.


    VB Code:
    1. 'ThisWorkBook
    2. Option Explicit
    3.  
    4. Private RD As clsMyEvents
    5.  
    6. Private Sub Workbook_Open()
    7.     Set RD = New clsMyEvents
    8.     MsgBox "My Events Created!", vbOKOnly + vbInformation, "My Excel Events"
    9. End Sub
    10.  
    11.  
    12. 'Name: clsMyEvents
    13. 'Instancing: Private
    14. Option Explicit
    15.  
    16. Public goXL As Excel.Application
    17.  
    18. Public WithEvents MyEvents As Excel.Workbook
    19.  
    20. Private Sub Class_Initialize()
    21.     Set goXL = GetObject(, "Excel.Application")
    22.     Set MyEvents = goXL.ActiveWorkbook
    23. End Sub
    24.  
    25. Private Sub Class_Terminate()
    26.     Set goXL = Nothing
    27. End Sub
    28.  
    29. Private Sub MyEvents_BeforeClose(Cancel As Boolean)
    30.     Dim iResp As Integer
    31.     iResp = MsgBox("RobDog888: Do you want to save your workbook?", vbYesNoCancel + vbQuestion, "My Excel Events")
    32.     If iResp = vbYes Then
    33.         goXL.ActiveWorkbook.Save
    34.         If goXL.ActiveWorkbook.Saved = True Then
    35.             MsgBox "Saved!", vbOKOnly + vbInformation, "My Excel Events"
    36.         Else
    37.             MsgBox "Error Saving Workbook!" & vbNewLine & "Sorry!", vbOKOnly + vbCritical, "My Excel Events"
    38.         End If
    39.     ElseIf iResp = vbCancel Then
    40.         Cancel = True
    41.     End If
    42. End Sub
    43.  
    44. Private Sub MyEvents_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    45.     MsgBox "No Right-Clicking!", vbOKOnly + vbInformation, "My Excel Events"
    46.     Cancel = True
    47. End Sub

    Edit: I added two workbooks. One for 97-2003 and the other for 2007.
    Attached Files Attached Files
    Last edited by RobDog888; Sep 11th, 2008 at 10:50 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  2. #2
    New Member
    Join Date
    Nov 2007
    Posts
    2

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

    when someone logs out of a page. how to make not go back to the previous page without loggin in first. in VB.NET.

  3. #3
    Addicted Member
    Join Date
    Sep 2008
    Posts
    128

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

    how do I adapt this piece of code for specific sheets only?

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

    Cancel = True

    End Sub

    at the moment it has worked on the sheet that calls the script but I still get the rightclick menu on my other sheets?

  4. #4

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,088

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

    I updated my first post to include two workbook versions of this example that are fully functioning. Just enable macros.
    It works on all sheets in the workbook. Make sure to save it first or run the Workbook_Open event.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.