CODEWITHSUNDEEP
vbaexcel
Sergey
Sergey

Reputation: 123

How to pass values from an array to a drop down list in a certain cell (not combo list) in excel VBA

I'm trying to pass values from an array to a drop down list in a specifc cell. Say I have an array which contains the values 1,2,3 and I want cell A1 to contain a drop down list with these value, Is there any way I can do this? I'm trying to achive this without having to first assign these value to different cells and use data validation. (And as stated in the title, I don't want to use combo boxes or user forms)

Upvotes: 2

Views: 10682

Answers (2)

Davy C
Davy C

Reputation: 649

This should give you a way of doing it:

Dim myArray
myArray = Array("1", "2", "3")

Range("A" & 1).Select

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=myArray(0) & "," & myArray(1) & "," & myArray(2)
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Upvotes: 3

Davesexcel
Davesexcel

Reputation: 6982

Here's a couple of ways, same result,

Sub DataVal1()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="1,2,3"
    End With
End Sub
Sub DataVal2()
    Dim x As String
    x = "1,2,3"
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=x
    End With
End Sub

Upvotes: 2

Related Questions