+ Reply to Thread
Results 1 to 1 of 1

Context menu item does not call subroutine?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-07-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    274

    Context menu item does not call subroutine?

    Hello,

    I have found this article that explains how to create a context menu when one right clicks a cell:

    http://sitestory.dk/excel_vba/popup-menu-eng.htm

    So I attempted to do just one item and here is what I entered in VBA:


    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''SMALL
    Sub Auto_Open()
       CreateShortcut
    End Sub
    
    Private Sub Worksheet_BeforeRightClick _
    (ByVal Target As Excel.Range, Cancel As Boolean)
    
    Dim rIsect As Range
    
    On Error GoTo ErrorHandle
    
    Set rIsect = Application.Intersect(Range("E4:E8"), Target)
    
    If Not rIsect Is Nothing Then
       CommandBars("MyShortcut").ShowPopup
       Cancel = True
    End If
    
    BeforeExit:
    Set rIsect = Nothing
    
    Exit Sub
    ErrorHandle:
    MsgBox Err.Description
    Resume BeforeExit
    End Sub
    
    
    Sub CreateShortcut()
    Dim myBar As CommandBar
    Dim myItem As CommandBarControl
    
    On Error Resume Next
    
    'Deletes any old popup menu.
    CommandBars("MyShortcut").Delete
    
    On Error GoTo ErrorHandle
    
    
    Set myBar = CommandBars.Add _
       (Name:="MyShortcut", Position:=msoBarPopup, Temporary:=True)
    
    
    Set myItem = myBar.Controls.Add(Type:=msoControlButton)
    With myItem
       .Caption = "&Menu item 1..."
       .OnAction = "Dummy1"
       .FaceId = 133
    End With
    
    Exit Sub
    ErrorHandle:
    MsgBox Err.Description & vbNewLine & _
    "Procedure CreateShortcut.", vbCritical, "Error"
    End Sub
    
    
    Sub Dummy1()
    MsgBox "Menu item 1"
    End Sub
    Although the context menu appears, it appears with 7 items instead of one and when I click on the menu item, the dummy function does not get called???

    Does anyone know what I am doing wrong?

    Thanks all for your help
    Rn
    Last edited by RustyNail; 04-25-2015 at 11:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to call a subroutine in another workbook
    By dgarris in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2008, 10:30 AM
  2. Context-sensitive custom menu item.
    By Murray in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-28-2005, 01:00 AM
  3. Call subroutine from another workbook
    By DejaVu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2005, 12:12 PM
  4. subroutine call with arguments
    By gabe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-11-2005, 06:21 PM
  5. How to disable the "Insert Copied Cells" context menu item
    By Coen in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 02-09-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1