Forum Discussion
Combo box programming with c#
Can someone can advise me how to programmatically set up a form control? I'll explain the problem on the example. My program opens a table Template.xlsx, completes the list of contact points, and saves it as a Form.xlsx. The end user should add a name to the Form.xlsx and select one point of contact. He uses a combo box control. When selects a contact point, their index is saved in cell C4, and the relevant cells show the street, city, zip code.
Program written in C#, populates the contact table with the current values, and insetrs the current formula values. I can't program the combo box control.
The macro record for setting up the combo box is:
ActiveSheet.Shapes.Range(Array("Drop Down 1")).Select
Application.CutCopyMode = False
With Selection
.ListFillRange = "List!$C$2:$C$5"
.LinkedCell = "C4"
.DropDownLines = 8
.Display3DShading = False
End With
I don't know how to write this sequence in C#.
private void CreateForm()
{
string templatePath = @"c:\Work\Template.xlsx";
string formPath = @"c:\Work\Form.xlsx";
if (File.Exists(formPath))
File.Delete(formPath);
var exApl = new Microsoft.Office.Interop.Excel.Application { Visible = false };
var wBook = exApl.Workbooks.Open(templatePath);
var wShtFrm = wBook.Worksheets["Form"];
var wShtLst = wBook.Worksheets["List"];
int iLast = 5;
for (int i = 2; i <= iLast; i++)
{
wShtLst.Cells[i, 1] = "CP" + i;
wShtLst.Cells[i, 2] = "Street " + i;
wShtLst.Cells[i, 3] = "City " + i;
wShtLst.Cells[i, 4] = 12300 + i;
}
wShtFrm.Range["B3"].FormulaR1C1Local = $"=INDEX(List!R2C1:R{iLast}C4;R4C3;1)";
wShtFrm.Range["B4"].FormulaR1C1Local = $"=INDEX(List!R2C1:R{iLast}C4;R4C3;2)";
wShtFrm.Range["B5"].FormulaR1C1Local = $"=INDEX(List!R2C1:R{iLast}C4;R4C3;3)";
wShtFrm.Range["B6"].FormulaR1C1Local = $"=INDEX(List!R2C1:R{iLast}C4;R4C3;4)";
//=====================================================================
var _contrl = wShtFrm.Shapes.Range["Drop Down 1"];
//??????????
//_contrl.ListFillRange = $"List!$C$2:$C${iLast }";
//_contrl.LinkedCell = "C4";
//_contrl.DropDownLines = 4;
//_contrl.Display3DShading = false;
//=====================================================================
wShtFrm.Protect(Contents: true);
wShtFrm.EnableSelection = XlEnableSelection.xlUnlockedCells;
wShtLst.Protect(Contents: true);
wShtLst.EnableSelection = XlEnableSelection.xlUnlockedCells;
wBook.Close(true, formPath);
exApl.Quit();
}
4 Replies
Did you add the Microsoft.Office.Interop.Excel library to your project references?
Yes, at the beginning of the program is:
using Microsoft.Office.Interop.Excel;
using System.IO;
The last command executed successfully is:
var _contrl = wShtFrm.Shapes.Range["Drop Down 1"];
Command
_contrl.ListFillRange = $"List!$C$2:$C${iLast}";
throws this error message:
(System._ComObject does not contain a definition for ListFillRange)
The recorded Excel macro is:
ActiveSheet.Shapes.Range(Array("Drop Down 1")).Select
With Selection
.ListFillRange = "List!$C$2:$C$5"
.LinkedCell = "C4"
.DropDownLines = 8
.Display3DShading = False
End With
when I check the both codes given, in VBA you "select" the shape and use FillListRange method of selection object. In the C# version you create a var _cntrl and try to call this objects FillListRange method which simply doesn't exists as the variant object is a "shape" object.
In fact when I try to assign the shape object to a VBA Shape variable, the FillListRange method doesn't exists also.
The easiest way is to do the same in C# and select the shape and use FillListRange method of the selection. However it is better to use the "ActiveX ComboBox" item, not the standard combobox, as this item has an object property which has a ListFillRange method.
