CODEWITHSUNDEEP
excelvb.net
Bob
Bob

Reputation: 1396

VB.Net: Inserting Data Validation In Excel Cell

I'm trying to programmatically add a drop down box to a cell in an Excel Worksheet. For a very simple example I just want the drop down box to give the option of "Yes" or "No". After Googling, it sounds like I can do this rather easily by inserting data validation on the cell.

I'm running across the issue of finding an example on how to do this. All the examples I've found are outdated, too complex, or not in VB.Net. When I say too complex, I mean it's not a simple "Yes" "No" situation. They are using actual columns in a different worksheet.

Upvotes: 2

Views: 2016

Answers (2)

xldynoh
xldynoh

Reputation: 41

I have the same problem and tried what's been marked as a solution. The posted code actually does NOT work - exception is raised when trying to add the validation.

After some experimentation, I found that if you select the range first, then add the validation to the selection, it will work fine.

Dim xlRng As Excel.Range = xlWorksheet.Cells(1, 1)
xlRng.select   ' this line added
With xlApp.selection.Validation    ' change range to selection
    .Add(Type:=Excel.XlDVType.xlValidateList, _
         AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
         Operator:=Excel.XlFormatConditionOperator.xlBetween, _
         Formula1:="Yes,No")
    .IgnoreBlank = True
    .InCellDropdown = True
End With

Upvotes: 4

soohoonigan
soohoonigan

Reputation: 2350

If you just want a simple in-cell yes/no dropdown, you just have to declare a range and add a validation rule to it. Microsoft has a bit of documentation on that here, but as with most interop docs it's a little sparse...so here's a snippet showing how a validation list can be added:

    'Imports Microsoft.Office.Interop.Excel

    Dim xlApp As New Excel.Application
    xlApp.Visible = True
    Dim xlWorkbooks As Excel.Workbooks = xlApp.Workbooks
    Dim xlWorkbook As Excel.Workbook = xlWorkbooks.Add
    Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Worksheets(1), Excel.Worksheet)

    Dim xlRng As Excel.Range = xlWorksheet.Cells(1, 1)
    With xlRng.Validation
        .Add(Type:=Excel.XlDVType.xlValidateList, _
             AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
             Operator:=Excel.XlFormatConditionOperator.xlBetween, _
             Formula1:="Yes,No")
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRng)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbooks)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

Upvotes: 1

Related Questions