Solved

Excel VB.net Add-in Right click menu issue

Posted on 2013-01-12
2
591 Views
Last Modified: 2013-01-14
Hi
I have several add-ins running in Excel that I have built. In one of them I use the code
below to get a right click menu. It is used with a specific spreadsheet that has a pivot table
and overrides the usual right click menu.
My problem is that it does this for all spreadsheets. How do I add extra control to let
Excel run its usual right click in other spreadsheets and only run the customized one when
I need it to?


Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel


Public Class ThisAddIn

    Dim cellbar As Office.CommandBar
    WithEvents button As Office.CommandBarButton


    Private Sub ThisAddIn_Startup() Handles Me.Startup

        Try
            'NOTE - this menu is a brnd new one and overrides the existing Excel right click
            cellbar = Me.Application.CommandBars.Add("Testing", Office.MsoBarPosition.msoBarPopup, False, True)
            If button Is Nothing Then
                ' add the button
                button = DirectCast(cellbar.Controls.Add(Office.MsoControlType.msoControlButton, , , , True), Office.CommandBarButton)
                button.Caption = "Add/Edit Tasks"
                button.BeginGroup = True
                button.Tag = "MYRIGHTCLICKMENU"
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Try

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Private Sub button_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles button.Click
        Call oAddEdit_Tasks()
    End Sub

    Private Sub Application_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) Handles Application.SheetBeforeRightClick
        Cancel = True
        cellbar.ShowPopup()
    End Sub
0
Comment
Question by:murbro
2 Comments
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
Comment Utility
You could try testing for the workbook/worksheet name in the SheetBeforeRightClick event to determine wether to cancel the default action or not
Michael
0
 

Author Closing Comment

by:murbro
Comment Utility
Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now