CODEWITHSUNDEEP
c#excelvbaexcel-addinsexcel-dna
Jonny Piazzi
Jonny Piazzi

Reputation: 3784

How to use ExcelCommand from ExcelDna

I have a simple ExcelCommand:

[ExcelCommand(Name = "MyTestCommand", ShortCut = "^Q")]
public static void Teste()
{
    var xlApp = (Application)ExcelDnaUtil.Application;
    var ws = xlApp.Sheets[1] as Worksheet;
    var range = ws.Cells[1, 1] as Range;
    range.Value2 = "foo bar";
}

When I press Ctrl + Shift + Q, cell A1 from the first sheet receives the text "foo bar".

The client doesn't want a shortcut, he wants a user interface button (in Ribbon or in sheet body, doesn't matter).

With VBA I can write:

Sub Button1_OnClick()
    MyTestCommand
End Sub

But call MyTestCommand doesn't work.

How can I call my created command?

Upvotes: 2

Views: 1895

Answers (3)

Sergio Lobo
Sergio Lobo

Reputation: 1

Try this:

Sub Button1_OnClick()
    Application.Run("MyTestCommand")
End Sub

Upvotes: 0

jim tollan
jim tollan

Reputation: 22485

There is a VERY horrid frig that you could employ by using VBA's quirky SendKeys function. This would get things working without having to use the ribbon and would let you use standard buttons on your sheet.

ok, stand back and cover your eyes :)

Sub Button1_Click()
    ' this short cut invokes the
    ' MyTestCommand function
    SendKeys "^Q"
End Sub

endure, i mean enjoy

Upvotes: 1

C. Augusto Proiete
C. Augusto Proiete

Reputation: 27818

If you set a value for the MenuText member of ExcelCommand, it will automatically become available through the Add-Ins ribbon.

enter image description here

[ExcelCommand(MenuText = "My Test Command", ShortCut = "^Q")]
public static void Teste()
{
    // ...
}

If you want more control over the look-and-feel of how the command is presented, then create your own CustomUI, with your own button, icon, etc. Take a look at the different Ribbon examples that come with Excel-DNA.

Upvotes: 3

Related Questions