Excel C # Excel Dropdown Event
I have an Excel spreadsheet that is in an automated testing application that is written in C #. The testing app fills the worksheet with final statistics, and the table is then used to generate metrics. Part of this procedure is to place dropdown lists in column L of the spreadsheet and write related VBA change events to the sheet from the application.
Here is the code that writes the dropdown list to the 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 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());
This writes the following VBA events to the spreadsheet to take actions on the worksheet as the failures are flagged.
Sub expFail1_Change(ByVal Target As Range)
Call lstBox_Update("expFail1")
End Sub
All comboboxes call the same function (lstBox_Update) based on the selection.
Everything works fine. The dropdowns appear where they should and macros are written to the spreadsheet correctly. The problem seems to be related to the related change events being fired when the selection changes. The solution is probably easy, but I've looked everything and can't find an answer
source to share
I just made a small example based on your code. After some searching and testing some parts of the source code, it boils down to one line of code:
xlDropDown.Name = "expFail" + (x + 1).ToString(); // your code
xlDropDown.OnAction = xlDropDown.Name + "_Change"; // new code: IMPORTANT
Found this code in Microsoft KB article How to Create Excel Macro Using Automation with Visual C # .NET >.
You seem to need this line of code to set up some kind of delegate pointing to your custom vba code.
If you need my full source, let me know and I'll post it to you.
source to share
Based on this article, you need to write code to an event Worksheet_Change
, not an event ComboBox_Change
:
Sub Worksheet_Change(ByVal Target As Range)
Call lstBoxUpdate("expFail1")
End Sub
source to share