C#Excel下拉事件 [英] C# Excel dropdown event

问题描述

我有一个Excel电子表格，该电子表格位于用C#编写的自动测试应用程序中.测试应用会使用最终统计信息填充工作表，然后使用电子表格生成指标.该过程的一部分是将下拉列表放入电子表格的L列，然后将其相关的VBA更改事件从应用程序写入工作表.

I have an Excel spreadsheet that sits in an automated testing application that is written in C#. The testing app populates the sheet with final stats and the spreadsheet is then used to generate metrics. Part of that procedure is to place dropdowns into Column L of the spreadsheet, and write their associated VBA change events into the sheet from the app.

这是将下拉列表写入电子表格的代码 Excel.DropDown xlDropDown;

Here is the code that writes the dropdown to the spreadsheet Excel.DropDown xlDropDown;

//set range for insert cell
 range = wrkSheet.get_Range("L" + (x + 9).ToString() + ":L" + (x + 9).ToString());

//insert the dropdown into the cell
xlDropDown = xlDropDowns.Add((double)range.Left, (double)range.Top, (double)range.Width, (double)range.Height, true);

//set the nbame of the new dropdown
xlDropDown.Name = "expFail" + (x + 1).ToString();

//assign dropdown name to cmbName 
cmbName = xlDropDown.Name;

 //call function to write change macro for this box 
 cmbWriteMacro(cmbName, xlApp, xlBook, wrkSheet); 

cmbWrite函数:

The cmbWrite Function:

    StringBuilder sb;
    VBA.VBComponent xlModule;
    VBA.VBProject prj;
    string modName;
    int modExists;

    prj = wrkBook.VBProject;
    modExists = 0;

    sb = new StringBuilder();

    //build string with module code 
    sb.Append("Sub " + cmbName + "_Change()" + "\n");
    sb.Append("\t" + "Call lstBox_Update(" + cmbName + ")" + "\n");
    sb.Append("End Sub");

    foreach (VBA.VBComponent comp in prj.VBComponents)
    {
        modName = comp.Name;

        if (modName == "Module2")
        {
            modExists = 1;
            break;
        }
    }

     //check to see if module already exists
     if (modExists != 1)
     {
        //set an object for the new module to create
        xlModule = wrkBook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
     }
     else
     {
        xlModule = wrkBook.VBProject.VBComponents.Item("Module2");
     }

     //add the cmbbox macro to the spreadsheet
     xlModule.CodeModule.AddFromString(sb.ToString());

这会将以下VBA事件写入电子表格，以便在记录到故障时在工作表中执行操作.

This writes the following VBA events into the spreadsheet to perform action in the sheet as failures are noted.

Sub expFail1_Change(ByVal Target As Range)
    Call lstBox_Update("expFail1")
End Sub

所有下拉菜单根据选择内容调用相同的函数(lstBox_Update).

All the dropdowns call the same function (lstBox_Update) based on what the selection is.

一切正常.下拉菜单显示应该在的位置，并且宏已正确写入电子表格中.问题似乎是在更改选择时触发关联的更改事件.解决方案可能很简单，但我四处张望，似乎找不到答案

Everything is working OK. The dropdowns show up where they should and the macros are being written into the spreadsheet correctly. The problem seems to be getting the associated change events to fire when the selection is changed. The solution is probably easy, but I have looked all over and can't seem to find an answer

推荐答案

我只是根据您的代码编写了一个小示例.经过一番搜索并测试了一些源代码片段之后，它最终变成了单行代码:

I just did a small example based on your code. After some googling around and testing some source code pieces it comes down to a single line of code:

xlDropDown.Name = "expFail" + (x + 1).ToString(); // your code
xlDropDown.OnAction = xlDropDown.Name + "_Change"; // new code: IMPORTANT

在 Microsoft KB文章中找到了此代码 如何创建Excel通过使用Visual C#.NET中的自动化宏来实现..

似乎您需要此代码行来设置某种委托，以指向您的自定义vba代码.

It seems that you need this line of code to set some kind of delegate pointing to your custom vba code.

如果您需要我的完整资料，请告诉我，我会将其邮寄给您.

In case you need my complete source let me know and I will mail it to you.

这篇关于C#Excel下拉事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！