0

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?

3
Contributors
2
Replies
12
Views
5 Years
Discussion Span
Last Post by ruchirahuldoshi
0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.