Opening a User Form from C# code using excel DNA

[ankit mishra]

Hello All,

I have an existing VBA code which I am converting into C# using excel DNA. In one of my scenario user has to clicks on a button in ribbon which populates a user form with a list (this list comes from another sheet using name manager). User selects items from list (using check box) and click on 'Add' button. This adds selected records in a excel sheet.

Is there any way of creating user form from C# code? 

I am referring TestCSharp.xll  present at Samples\Ribbon in ExcelDna distribution.

Regards,

Ankit

[ankit mishra]

Referred This:

https://groups.google.com/forum/#!searchin/exceldna/call$20to$20vba$20function|sort:relevance/exceldna/pzjswfw1Now/RvZ6mDL0M_QJ

And used xlUDF and xlcRun but got error "Can not run the macro TestMacro..." ( I created TestMacro in Excel sheet to call Userform)

Then found this and realized that Macro call won't work directly from C# method assigned to ribbon's button

https://groups.google.com/forum/#!searchin/exceldna/can$20not$20run$20macro$20workbook|sort:relevance/exceldna/z9bB_2gvGbI/1_oGezfpCQAJ

So created a another class with a Function to call Macro, which works !!!

My current code

//Code for calling Macro defined inside excel's module

    public class InterfaceFunctions

    {

        public String openUserForm()

        {

            String msg = "Error is: ";

            try

            {

                dynamic xlApp = ExcelDnaUtil.Application;

                xlApp.Workbooks["Book1.xlsm"].Activate();

                xlApp.Run("TestMacro");

            }

            catch (Exception ex)

            {

                msg = msg + ex;

                return msg;

            }

            msg = null;

            return msg;

        }

    }

And Function which is called by ribbon button

public static void addDatatToSheet()

        {

            

            Microsoft.Office.Interop.Excel.Application Application = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;

            

            Worksheet sheet1Ws = Application.Worksheets["Sheet1"] as Worksheet;

            sheet1Ws.Cells[1,4].Value2 = null;

            //XlCall.Excel(XlCall.xlUDF, "TestMacro"); dosen't work

            //XlCall.Excel(XlCall.xlcRun, "TestMacro"); dosen't work

            try

            {

                InterfaceFunctions instance = new InterfaceFunctions();

                String result =  instance.openUserForm();

                if (result!=null)

                {

                    MessageBox.Show(result);

                }

               

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error: " + ex);

            }

        }

Now the questions are:

1. Is this the right approach? 

2. Is there a way I can get sheet name from button (ie addDatatToSheet  Function) itself rather than hardcoding in  openUserForm function.

Regards,

Ankit