Creating Dropdown in excel using VB.NET

[kothaisaravan]

Hi

Using VB.NET I created an excel sheet with list of items in dropdownlist.

Using the below code

With xlWorksheet.Range("A2", "A101").Validation
.Add(Type:=Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, AlertStyle:=Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, Formula1:="Construct,Testing,Review,Look Ahead Meetings,Process Audit,Process Improvement,Project Monitoring and control,Project Planning,Project Setup,Project Team Management,RCA Activities,Re-Estimation,Review,Review-Rework,Senior Management Reviews, Testing,Testing Rework,Training, Work Product Audit")
End With

But i can able to view the listbox upto "testing", rest of the items are not diplayed in the listbox why?

Is there any limit for dropdown list items, If so how to increase the limit?

[Phasma]

From http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx
The maximum number of entries that you can have in a drop-down list is 32,767

As for why your items are not showing up I'd check for a typo.

Here is a simple macro I recorded for getting a dropdownlist of 212 items

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("E1").Select 'Address Where the DropDownList is to be
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$1:$A$212"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

[ruchirahuldoshi]

Thanks a lot for the help. You have solved my biggest problem.