Drop down with VBA, string length issue

[ogo]

I am using the following code to create a drop down,. The data stored in variable Mystring is comma separated strings and is very long and so it throws runtime error 1004. Is there any other way to create drop down with VBA?
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Mystring
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


Thanks,
Ogo

 

[AlphaFrog]

You could add an ActiveX type combobox.

    Dim oleCmb As OLEObject
    
    With ActiveCell
        Set oleCmb = ActiveSheet.OLEObjects.Add( _
                                 ClassType:="Forms.ComboBox.1", _
                                 Link:=False, _
                                 DisplayAsIcon:=False, _
                                 Left:=.Left, _
                                 Top:=.Top, _
                                 Width:=.Width + 1, _
                                 Height:=.Height + 1)
    End With
    oleCmb.LinkedCell = ActiveCell.Address
    oleCmb.Object.List() = Split([COLOR="Red"]Mystring[/COLOR], ",")
    oleCmb.Object.ListRows = 10

 

[ogo]

thanks for the reply, but i can't use an external object like combobox, as i am doing some other operations in the cell like copy,paste and delete..It is not easy to manage with oleobject
thanks,
ogo

 

[Weaver]

Am I right in thinking you can't dump your list into a range of cells and then use this range to populate your validation/listbox?

 

[GTO]

" The data stored in variable Mystring is comma separated strings "

What do you mean strings (plural)? Can you show us what MyString's value is?

 

[AlphaFrog]

In the ActiveX combobx code I gave, the cell the combobox is located in is also linked to the combobox. So you could copy\paste\delete to that cell and the combobox would respect the changes to the cell.

Alternativly, I think you can split Mystring and put each comma separated value in a column of cells. Then you can have the Data Validation list reference the Column range as the list source. Using a range as a list source allows you to have very long list. Longer than the 255 character limit when using a single string as the list.

This splits Mystring and pastes the individual values in column A. Then sets the source for the Data Validation list to column A.

    Dim v As Variant
    
    v = Split(Mystring, ",")

    Range("A1").Resize(UBound(v) + 1) = Application.Transpose(v)
    
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & Range("A1").Resize(UBound(v) + 1).Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

 

[ogo]

Yes, thanks very much..