Results 1 to 9 of 9

Don't make double items in context menu

Threaded View

  1. #1
    Registered User
    Join Date
    01-01-2010
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    34

    Don't make double items in context menu

    Hello,

    Under the right-click menu I added an item on a specific range in Excel. The location in Excel knows what to add into the menu (a for creating a heading or a new line item). When I right-click somewhere else the custom menu is removed. So far so good.

    When a right-click follows a right-click multiple times the menu item is added again and again. The routine should check weather the items exists and if now, create.

    This is what I made (with some help of 'Google')

    Right-click code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
        If Not Application.Intersect(Target, Me.Range("A13:A31”)) Is Nothing Then
    '        MsgBox ("This is the right-click range in this sheet.")
            If Target.Cells.Value = "" Then Exit Sub 'gives an error when the full row is selected instead of one cell
            If Target.Cells.Value Like "?0" Then
    '            MsgBox "Heading"
                Run "CustomContextMenuKopjeAdd"
                Run "CustomContextMenuPostRemove" 'when a heading, remove menu item
            Else
    '            MsgBox "Item"
                Run "CustomContextMenuKopjeRemove" 'when a item, remove menu heading
                Run "CustomContextMenuPostAdd"
            End If
        Else
            ' No cell of Target in in the range A14:A31. Get Out.
            Run "CustomContextMenuKopjeRemove"
            Run "CustomContextMenuPostRemove"
            Exit Sub
        End If
    End Sub
    Create and remove code:
    Private Sub CustomContextMenuPostAdd()
    Dim ctrl As CommandBarControl
    Dim btn As CommandBarControl
    Dim i As Integer
    
    'MsgBox "Context menu: make item"
        
        'add a 'popup' control to the cell commandbar (menu)
        Set ctrl = Application.CommandBars("Cell").Controls.Add _
                    (Type:=msoControlPopup, Before:=1)
        ctrl.Caption = "SSK p&ost..."
        'first sub item
            Set btn = ctrl.Controls.Add
            btn.Caption = "&Add" 	   	'give them a name
            btn.OnAction = "RowNew"                   'the routine called by the control
        'second sub item
            Set btn = ctrl.Controls.Add
            btn.Caption = "&Remove" 	     'give them a name
            btn.OnAction = "RowRemove"                   'the routine called by the control
    End Sub
    
    
    Private Sub CustomContextMenuPostRemove()
    Dim ctrl As CommandBarControl
        
    'MsgBox "Context menu: remove item"
         
        'go thru all the cell commandbar controls and delete our menu item
        For Each ctrl In Application.CommandBars("Cell").Controls
            If ctrl.Caption = "SSK p&ost..." Then ctrl.Delete
        Next
    
    End Sub
    Does anybody know what I did wrong or how I can improve this?

    Thank you in advance!
    Erik
    Attached Images Attached Images

Thread Information

Users Browsing this Thread

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

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