VB.Net: Inserting Data Validation In Excel Cell

Question

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.

xldynoh · Accepted Answer · 2018-04-15 16:28:21Z

4

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

answered Apr 15, 2018 at 16:28

xldynoh

414 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rann Lifshitz Over a year ago

Well answered. Good job buddy!

soohoonigan · Accepted Answer · 2017-07-06 17:13:49Z

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)

Collectives™ on Stack Overflow

VB.Net: Inserting Data Validation In Excel Cell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related