asked on

how to dynamically create a dropdown list for excel cell in .NET

how to write .net code to create a dropdown list to be used in excel cell for data validation
like worksheet.dropdowns...

' Start Excel and get Application object.
         objExcel = New Excel.Application()
 
         ' Get a new workbook.
         objWorkBook = objExcel.Workbooks.Add()
         objWorkSheet = objWorkBook.ActiveSheet
         objWorkSheet.Name = "xxx"
            strCellName = ExcelColumn(iCol) & "1"
            objWorkSheet.Range(strCellName, strCellName).Font.Bold = True
            objWorkSheet.Range(strCellName, strCellName).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            objWorkSheet.Range(strCellName, strCellName).MergeArea.EntireRow.Interior.ColorIndex = 15
            objWorkSheet.Range(strCellName, strCellName).ColumnWidth = objCTDef.ColData_Info.ColWidth / 10
            objWorkSheet.Range(strCellName, strCellName).Locked = True

Open in new window

Rory Archibald

Something like this I guess:

With objWorkSheet.Range(strCellName, strCellName).Validation
        .Delete
        .Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:="=$O$1:$O$6"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

xyzlucky

ASKER

thanks, how would I load the data to the dropdown list

Rory Archibald

That code uses a range (O1:O6 in this case). If you wanted a hard coded list, you would assign a comma-delimited string instead of the range as the Formula1 property. Note that that is limited to 255 characters.

xyzlucky

ASKER

I tried to load data to O1:O6 but it keesp on abending...and I tried to put the comma dellimited test string like
.Add(Type:=3, AlertStyle:=1, Operator:=1, Formula1:="1,2,3,4,5,6") and it still abended. What did i do wrong here?

xyzlucky

ASKER

this is the code and the attached image shows how it displays. I am not sure what I did wrong her?
objWorkSheet.Range(strCellName, strCellName).Font.Bold = True
objWorkSheet.Range(strCellName, strCellName).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
objWorkSheet.Range(strCellName, strCellName).MergeArea.EntireRow.Interior.ColorIndex = 15
objWorkSheet.Range(strCellName, strCellName).ColumnWidth = objCTDef.ColData_Info.ColWidth / 10
objWorkSheet.Range(strCellName, strCellName).Locked = True

objWorkSheet.Cells(2, 6) = "1"
objWorkSheet.Cells(3, 6) = "2"
objWorkSheet.Cells(4, 6) = "3"
objWorkSheet.Cells(5, 6) = "4"
objWorkSheet.Cells(6, 6) = "5"
objWorkSheet.Cells(7, 6) = "6"
'.Add(Type:=3, AlertStyle:=1, Operator:=1, Formula1:="=$O$1:$O$6")

With objWorkSheet.Range(strCellName, strCellName).Validation
.Delete()
.Add(Type:=3, AlertStyle:=1, Operator:=1, Formula1:="$F$2:$F$7")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Image2.jpg

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial