Keyboard Shortcuts To Format Cells - Excel DNA C#

[Garrett Womack]

Hi,

I am currently struggling to assign functions to keyboard shortcuts in Excel DNA through the C API integration (xlCall).

Please note that in the code below, the "GW_Addon.CellNumberFormat" calls return a property that is a string that represents the number formatting I would like to use.

Here is the code I'm trying to execute:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ExcelDna.Integration;

using Excel = Microsoft.Office.Interop.Excel;

namespace GW_Addons

{

    class NumberFormatingHotkeys : IExcelAddIn

    {

        public void AutoOpen()

        {

            XlCall.Excel(XlCall.xlcOnKey, "+^2", "CleanThousandsForamt");

            XlCall.Excel(XlCall.xlcOnKey, "+^3", "CleanDateFormat");

            XlCall.Excel(XlCall.xlcOnKey, "+^6", "CleanNumberFormat");

        }

        public void AutoClose()

        {

            XlCall.Excel(XlCall.xlcOnKey, "+^2", "CleanThousandsForamt");

            XlCall.Excel(XlCall.xlcOnKey, "+^3", "CleanDateFormat");

            XlCall.Excel(XlCall.xlcOnKey, "+^6", "CleanNumberFormat");

        }

        /// <summary>

        /// Formats dates to show Month - Year.  Example Feb-17

        /// </summary>

        [ExcelCommand]

        public static void CleanThousandsForamt()

        {

            Excel.Range selection = GW_Addons.ActiveExcelRefs.GetActiveSelection();

            selection.NumberFormat = GW_Addons.CellNumberFormats.CleanNumberFormat;

        }

        /// <summary>

        /// Formats numbers to show no decimals and commas.  Example: 1,234

        /// </summary>

        [ExcelCommand]

        public static void CleanDateFormat()

        {

            Excel.Range selection = GW_Addons.ActiveExcelRefs.GetActiveSelection();

            selection.NumberFormat = GW_Addons.CellNumberFormats.CleanDateFormat;

        }

        /// <summary>

        /// Scales numbers to thousands.  1,234,567 becomes 1,235 (rounding)

        /// </summary>

        [ExcelCommand]

        public static void CleanNumberFormat()

        {

            Excel.Range selection = GW_Addons.ActiveExcelRefs.GetActiveSelection();

            selection.NumberFormat = GW_Addons.CellNumberFormats.CleanNumberFormat;

        }

    }

}

When I try to use the keyboard shortcuts I receive this error in excel:

I believe that Excel is trying to reference a macro that would be contained either in a VBA add-in file or in the book its self.  Is it possible to execute a c# subroutine on a hotkey using the C API integration?

Thanks,

Garrett

[Govert van Drimmelen]

Hi Garett,

Your class must be public for the macros to be registered with Excel.

By mistake I support private classes that implement IExcelAddIn, so your AutoOpen code does run. I think it's too late to change that now... but it's kind of wrong and confusing.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Garrett Womack]

Thank you so much Govert!!!  You are my hero!