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ᴇʜ, Apr 12 '18 at 09:20
I know this s not a free code service. Problem in my system. Was trying to edit my post. — aaroki, Apr 12 '18 at 09:28
What you want to do is `split` the values in the first column. It is possible to do so, [without vba](https://support.office.com/en-us/article/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68). I think you may find [this post](https://stackoverflow.com/questions/27564766/transforming-multiple-entries-in-a-cell-into-multiple-rows) usefull to integrate into your generated code — fictimaph, Apr 12 '18 at 09:41

score 1 · Answer 1 · answered Apr 12 '18 at 09:42

1

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

answered Apr 12 '18 at 09:42

Pᴇʜ

56,719
10
49
73

ya , its working fine. but when i did some changes and closed the excel. And if i try to open the same, its repairing that excel and that sheet moved to the next sheet. Dropdown values also not coming. – aaroki Apr 12 '18 at 14:25
Well, repairing means that probably your Excel file is corrupt. That has nothing to do with the code. Try to make a new workbook and copy/past everything into to new workbook. OR try to save it in the binary XLSB format (which often is more stable). – Pᴇʜ Apr 12 '18 at 14:29
no, it has totally 8 sheets. each one has different functionality. Actually , there are some other columns also dropdown in this sheet. but, now i commented this set of code and did the save and opened again. its working as expected. – aaroki Apr 12 '18 at 14:31
And am doing it in WorkSheet_Activate(). – aaroki Apr 12 '18 at 14:32
"*Will it cause of pbm?*" What? I don't understand you, sorry. – Pᴇʜ Apr 12 '18 at 14:33
am creating the dropdown list when activate the worksheet not when opening it. Is it fine? – aaroki Apr 12 '18 at 14:35
Well, If it works for you. Yes. If this solved your question please mark this answer as solution – Pᴇʜ Apr 12 '18 at 14:35
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/168850/discussion-between-aaroki-and-p). – aaroki Apr 12 '18 at 14:41

Create dropdown list from comma separated values when workbook open

1 Answers1