VBA to remove blanks from Drop Down List going above 255 characters HELP

[baggieade]

Hi

I have some code that loops through a named range called People_Names to create a string called mylist that can be used in a drop down box.

It works great except for when the string called mylist goes beyond 255 characters.

This is my vba code.

' find all non empty people names
myList = ""
For Each name In People_Names
If name = "" Then
'do nothing
Else
myList = myList & name & ","
End If
Next name
' add the new list to the cells on allocations for people
If myList = "" Then
'do nothing
Else ' update the list
For n = 5 To 30
With Allocation.Cells(n, 4).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
End With
Next n
End If

Is there an alternative method to get around this. I've tried creating a variable array and using that for the drop down but that didn't work either.,

[xlnitwit]

Hi,

You need to use a range. No other method will work, unless you want to use code to remove the validation whenever the workbook is closed and reset it on opening again.

[AlphaFrog]

An alternative could be to use ActiveX comboboxes instead of data validation.

This will add comboboxes to cells D5:D50. You only have to run this one time to setup the comboboxes.

Please Login or Register  to view this content.

This will populate the comboboxes with the names

Please Login or Register  to view this content.