Create dropdown list from comma separated values when workbook open

Question

I have one column has comma separated values populating from DB. The Range could be anything.

When open the workbook that comma separated column should be dropdown with values using vba.

I am very new to this macro. I have written the below code. its working fine for single cell. But, I want to do this for certain range like example "H" Column from H1 to last. How to achieve this? Can help me?

Private Sub Workbook_Open()
    AddListValidation "Task", "A1", "A2"
End Sub


Sub AddListValidation(sheet, cellSource As Range, cellTarget As Range)
    Dim Lastrow As Integer
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


    txt = ActiveWorkbook.Worksheets(sheet).Range(cellSource).Value
    ActiveWorkbook.Worksheets(sheet).Range(cellTarget) = "Select your values here"
    With ActiveWorkbook.Worksheets(sheet).Range(cellTarget).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="xxx,yyy,zzz"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Well what exactly is your question, you didn't ask one? This is not a free code writing service. You will need to start on your own and then if you get stuck show the code you already have and ask a question to it. — Pᴇʜ
I know this s not a free code service. Problem in my system. Was trying to edit my post. — aaroki
What you want to do is split the values in the first column. It is possible to do so, without vba. I think you may find this post usefull to integrate into your generated code — fictimaph

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-04-12T09:42:08

You will need a loop to call AddListValidation for every row. Also you don't need a parameter for the worksheet when you give full qualified ranges as parameters instead of addresses.

It is recommended always to use Long instead of Integer in VBA there is no benefit in using Integer at all. Especially for row counts because Excel has more rows than Integer can handle.

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Tabelle4")

    Dim LastRow As Long 'always use long instead of integer
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim iRow As Long
    For iRow = 2 To LastRow
        AddListValidation ws.Cells(iRow, "A"), ws.Cells(iRow, "B")
    Next iRow
End Sub


Sub AddListValidation(cellSource As Range, cellTarget As Range)
    cellTarget.Value = "Select your values here"
    With cellTarget.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=cellSource.Value
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Create dropdown list from comma separated values when workbook open

1 Answers