Search code examples
asp.netexcelvb.netspreadsheetgear

Make drop down list default value selected in Excel using spreadsheet gear + VB.Net

I am using below code to create a dropdownlist in Excel with "Yes,No" vallues displayed, but initially it is showing blank

worksheet.Range("I4:I5").Validation.Add(ValidationType.List, ValidationAlertStyle.Information, ValidationOperator.Default, "Yes,No", Nothing)

Initially I want default to set to "No", and the excel cell should be appear with arrow initially- which is not happening

Please help me out, how to do this using spreadsheet gear in VB.Net

THanks Ramesh

Solution

  • Adding validation to a cell does not automatically populate that cell with a given value, nor does it select the cell (which would make the "arrow" appear, which I think is a reference to the dropdown handle?).

    To make these additional things happen, you'll need to explicitly set the cell value via the IRange.Value property and select the desired cell via the IRange.Select() method. Example:

    ' Get reference to range affected.
Dim range = worksheet.Range("I4:I5")

' Apply validation.
range.Validation.Add(ValidationType.List, ValidationAlertStyle.Information, 
    ValidationOperator.Default, "Yes,No", Nothing)

' Set initial values of cells to "No".
range.Value = "No"

' Select the first cell in the affected range, I4.  Calling select
' directly on "range" would select both I4 and I5.
range(0, 0).Select()