Combo box programming with c#

Copper Contributor

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.

Form2.GIF

 

 

List1.GIF

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

@Pavel_Office 

Did you add the Microsoft.Office.Interop.Excel library to your project references?

@erol sinan zorlu 

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:

 
 Pavel_Office935_0-1590152928320.png

 

 

(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

 

 

@Pavel_Office935 

 

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.

@erol sinan zorlu 

Thank you, it's already working!

So the correct translation from VBA to C# is:

ShtFrm.Shapes.Range["Drop Down 1"].Select();

exApl.Selection.ListFillRange = $"List!$C$2:$C${iLast }";

exApl.Selection.LinkedCell = "C4";

exApl.Selection.DropDownLines = 4;

exApl.Selection.Display3DShading = false;

It is actually simple.

I thought that when the application is running as Visible = false ,I should not use the command Select()

Thank you again!