none
C# and onkey RRS feed

  • Question

  •  

    I try to use application.onkey in c# to trap Shift-F9 and it doesn't work as expected.

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual), 
    GuidAttribute("12B22828-5CDD-4AED-B888-4AE8B84ED7B3"), 
    ProgId("Jedox.Palo.XlAddin.Connect")]
    public class Connect : Object, Extensibility.IDTExtensibility2, System.IDisposable
    {
        public bool HandleSHIFTF9()
        {
          // ....
          System.Windows.Forms.MessageBox.Show("SHIFT-F9");
          return true;
        }
    
    	public void OnStartupComplete(ref Array custom)
    	{
          // ...
          ExcelHelper.oExcelApp.OnKey("+{F9}", "HandleSHIFTF9");
        }
    }
    

    But when I then press Shift-F9 Excel complains that it can't find the macro "HandleSHIFTF9" .

    What is missing ?

     

    with best regards

       Hendrik Schmieder

     

     

     

     

     

    • Moved by Calvin_GaoModerator Friday, May 13, 2011 5:36 AM C# related (From:Visual Studio Tools for Office)
    • Moved by Larcolais Gong Friday, May 13, 2011 12:42 PM (From:Visual C# General)
    Thursday, May 12, 2011 10:56 AM

Answers

  • Sorry to bother you again,

    you need to understand, that the OnKey Method is an Method that the Excel COM API provides to call a VBA-Function in Excel.

    This has nothing todo with C# or .Net.

    As I know there is no way to call a function in an external DLL like your AddIn - and so a pure C# solution is not possible - but if you find a solution let me know.

    Using a Script function that calls your AddIn code is the workaround to call .Net code.

    An Idea would be eventually to inject the VBA code dynamically by the AddIn.
    However - I'm not an expert and you can fully ignore this post.

    Greets - Helmut


    Helmut Obertanner [http://www.x4u.de] [http://www.outlooksharp.de]
    • Marked as answer by Bruce Song Tuesday, May 24, 2011 2:12 AM
    Friday, May 13, 2011 7:06 AM
    Answerer
  • Doesn't realy help,

    since customers wouldn't be happy if I would attach VBA code to thier workbooks.

     

    I have found a way without VBA.

    For this you need a XLL (which can be loaded by application.RegisterXLL).

    In this xll you register a function "HandleSHIFTF9" as 'R#' (replace R by U for Excel 2007 and newer).

    In "HandleSHIFTF9" you call back to your C# code via COM.

    then

    ExcelHelper.oExcelApp.OnKey("+{F9}", "HandleSHIFTF9");

    works.

    It is a lit bit tricky, but the best solution I've found sofar.

    Isn't there really a straightforward C# solution ?

    with best regards

       Hendrik Schmieder

     

     

     

     

    • Proposed as answer by Bruce Song Monday, May 23, 2011 9:27 AM
    • Marked as answer by h_schmieder Monday, May 23, 2011 1:58 PM
    Monday, May 16, 2011 8:54 AM
  • Hi Hendrik,

    I think Helmut is right.

    As far as I know, we can't use the OnKey with the pure C# solution.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by h_schmieder Monday, May 23, 2011 1:58 PM
    Wednesday, May 18, 2011 10:05 AM

All replies

  • Hi,

    see if that may help:
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/7b95e555-4bea-4815-8b4c-2d40093a3df6/

    Greets - Helmut


    Helmut Obertanner [http://www.x4u.de] [http://www.outlooksharp.de]
    Thursday, May 12, 2011 12:15 PM
    Answerer
  • this is another issue and vba related
    Thursday, May 12, 2011 12:21 PM
  • Hello,

    this is exactly your issue, you want to call code in your Managed Assembly on ExcelOnKey event.

    You need to have a VBA-Macro that works as a "bridge" between VBA and the .Net code.

    Greets - Helmut


    Helmut Obertanner [http://www.x4u.de] [http://www.outlooksharp.de]
    Thursday, May 12, 2011 12:33 PM
    Answerer
  • No it is not.

    What I have not said eplictly:

    I'm looking for a pure C# solution, otherwise onkey would be rather useless in C#.

    BTW.

    Had to remove

    ClassInterface(ClassInterfaceType.AutoDual), 

    from my C# code

     

    Thursday, May 12, 2011 1:40 PM
  • No it is not.

    What I have not said eplictly:

    I'm looking for a pure C# solution, otherwise onkey would be rather useless in C#.

    Hi,

     

    Well, I think you need some professional suggestions from C# experts. I’m going to move this thread to C# forum for better response.

     

    Thanks for your understanding and support.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 13, 2011 5:35 AM
    Moderator
  • Sorry to bother you again,

    you need to understand, that the OnKey Method is an Method that the Excel COM API provides to call a VBA-Function in Excel.

    This has nothing todo with C# or .Net.

    As I know there is no way to call a function in an external DLL like your AddIn - and so a pure C# solution is not possible - but if you find a solution let me know.

    Using a Script function that calls your AddIn code is the workaround to call .Net code.

    An Idea would be eventually to inject the VBA code dynamically by the AddIn.
    However - I'm not an expert and you can fully ignore this post.

    Greets - Helmut


    Helmut Obertanner [http://www.x4u.de] [http://www.outlooksharp.de]
    • Marked as answer by Bruce Song Tuesday, May 24, 2011 2:12 AM
    Friday, May 13, 2011 7:06 AM
    Answerer
  • Consider I have a file foo.vb with

    Public Sub HandleSHIFTF9()
    	MsgBox("SHIFT-F9")
    End Sub
    
    How can I load foo.vb into Excel from my C# code ?

    Friday, May 13, 2011 12:37 PM
  • Have a look at this:

    maybe that would help.

    You just need to do it in C#...

    http://msdn.microsoft.com/en-us/library/ff194737.aspx

    http://www.ozgrid.com/forum/showthread.php?t=71076&page=1

    Another Method would be to create an Excel Template with your VBA code and attach that at runtime.

    Greets - Helmut


    Helmut Obertanner [http://www.obertanner.de] [http://www.outlooksharp.de]
    Friday, May 13, 2011 1:20 PM
    Answerer
  • Doesn't realy help,

    since customers wouldn't be happy if I would attach VBA code to thier workbooks.

     

    I have found a way without VBA.

    For this you need a XLL (which can be loaded by application.RegisterXLL).

    In this xll you register a function "HandleSHIFTF9" as 'R#' (replace R by U for Excel 2007 and newer).

    In "HandleSHIFTF9" you call back to your C# code via COM.

    then

    ExcelHelper.oExcelApp.OnKey("+{F9}", "HandleSHIFTF9");

    works.

    It is a lit bit tricky, but the best solution I've found sofar.

    Isn't there really a straightforward C# solution ?

    with best regards

       Hendrik Schmieder

     

     

     

     

    • Proposed as answer by Bruce Song Monday, May 23, 2011 9:27 AM
    • Marked as answer by h_schmieder Monday, May 23, 2011 1:58 PM
    Monday, May 16, 2011 8:54 AM
  • Hi Hendrik,

    I think Helmut is right.

    As far as I know, we can't use the OnKey with the pure C# solution.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by h_schmieder Monday, May 23, 2011 1:58 PM
    Wednesday, May 18, 2011 10:05 AM