How Do You Create a Drop List Of Values during run-time?


#1

My template excel sheet has a named range of cells used as the values for a drop down list on another worksheet. When I copy my template to my new file, the drop-downs are gone. So, how can these be created during runtime? There does not seem to be a way to assign a named cell range as the source for a validation drop list…

Mike


#2

Hi Mike,

Do you use Excel.Copy method to copy the template and the drop-downs are gone? I fixed this bug. Please download hotfix at http://www.aspose.com/Products/Aspose.Excel/Fixes/Aspose.Excel.zip.

The following is the sample code to create validation drop list:

Excel excel = new Excel();
excel.Worksheets.Add();
Worksheet sheet = excel.Worksheets[1];
Range range = sheet.Cells.CreateRange(2, 5, 4, 1);
range.Name = "MyRange";

Validations validations = excel.Worksheets[0].Validations;
Aspose.Excel.Validation validation = validations[validations.Add()];
validation.Type = Aspose.Excel.ValidationType.List;
validation.Formula1 = "=MyRange";

CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;

validation.AreaList.Add(area);


#3

Laurence, Thanks for the speedy reply. I will give that a try. I used an object copy from Excel Template to New Excel File in VB. Here is the code. When I tried to use the Copy method on the worksheet level it copied blank worksheets. With this method it seems to work for everything except the drop list being linked to the cells they need to be used in.

Dim SS As New Aspose.Excel.Excel

Dim SSNew As New Aspose.Excel.Excel

Try

SS.Open(path & “\template.xls”)

SSNew = SS

Catch ex As Exception

End Try