Detecting when a user clicks a cell?

[DavedAndConfuscius]

This can be done in VBA (registering a function that is called on each cell selection / click) - but doesn't seem to be possible in ExcelDNA.  Am I missing something in the documentation, or is this just not possible?

On a related topic - I'm assuming we can't register a new right-click cell menu action?

Many thanks!

[Govert van Drimmelen]

Hi David,

With Excel-DNA you have full access to the Excel COM Automation interfaces. So anything that you can do in VBA can also be done in and Excel-DNA add-in (and more).

I've made a small example that shows how this might work. These instructions might work (if the stars are well aligned):

* Create a new Visual Basic Class Library project in Visual Studio.

* Add the following NuGet Packages:  "Excel-DNA", and "Excel-DNA.Interop". IN the Nuget dialog the second one is called "Excel-DNA - Primary Interop Assemblies for Excel 2010.

* Replace all the code in Class1.vb with the code pasted below.

* Press F5 to compile and run, then look for the selections changes to be displayed in the status bar, and try the cell context menu.

If anything goes wrong, write back and we can try to figure it out.

-Govert

'--------------------------------------

Imports Microsoft.Office.Interop.Excel

Imports Microsoft.Office.Core

Imports ExcelDna.Integration

Public Module MyFunctions

    <ExcelFunction(Description:="My first .NET function")> _

    Public Function dnaHello(name As String) As String

        Return "Hello " & name

    End Function

    

End Module

Public Class AddIn 

    Implements IExcelAddIn

    WithEvents Application As Application

    WithEvents Button As CommandBarButton

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        Application = ExcelDnaUtil.Application

        ' Add Cell context menu

        Dim ContextMenu As CommandBar

        ContextMenu = Application.CommandBars("Cell")

        Button = ContextMenu.Controls.Add(Type := MsoControlType.msoControlButton, Before := ContextMenu.Controls.Count, Temporary := true)

        With Button

            .Caption = "Excel-DNA Test Button"

            .Tag = "EXCEL-DNA-Test"

        End With

    End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

        Button.Delete

    End Sub

    Private Sub Application_SheetSelectionChange(Sh As Object, Target As Range) Handles Application.SheetSelectionChange

        Application.StatusBar = "Selection on sheet " + CType(Sh, Worksheet).Name & " is now " & Target.Address 

    End Sub

    Private Sub Button_Click(Ctrl As CommandBarButton, ByRef CancelDefault As Boolean) Handles Button.Click

        Application.StatusBar = "Excel-DNA Test Button - Clicked!"

    End Sub

End Class

[David Ford]

Awesome Govert ! Much appreciated!  Works well!

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/_z9i7NMZz6c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
Best regards

David Ford
0421 659 552
24.6.2013

[David Ford]

Actually - one issue; adding (in C#) the event handler seems tricky. The following doesn't seem to do anything (eg no response on a click, function myHandler isn't called):

Button.Click += new EventHandler(myhandler);

In part because this example: http://msdn.microsoft.com/en-us/library/0batekf4(v=vs.90).aspx

seems to indicate we need to use the Excel Core PIA assembly?

to do:

menuCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(myHandler);

Needless to say - this seems odd, and I'd prefer to not need this assembly added.

Any thoughts?

Cheers

Daved...

[Govert van Drimmelen]

Hi David,

I think your Click event handler needs to have the right signature, and "EventHandler" is not right.

It's a pain in C# to add the event handlers without references to the interop library.

If you do add a reference to the interop library (either by just adding a reference to the version on your machine, or by adding the "Excel-DNA.Interop" NuGet package, you still probably don't need to distribute the PIA assemblies. With .NET 4.0 there is the "Embed Interop Types" options (switched on by default) that will embed just those parts of the interop assemblies that you used.

Why would you prefer not to add the interop assembly, leaving the embed option on so you don't have to ship it?

-Govert