CODEWITHSUNDEEP
excelvbalistobject
radeg
radeg

Reputation: 61

How can I populate an in-cell dropdown list based on table values?

I am trying to create a dynamic in-cell dropdown list using VBA. Currently I have the dropdown list populated based on the values I have inputted. However, as I foresee the use for this program growing, I would like to make the dropdown list dynamic. Is there a way to have VBA loop through a table and populate the dropdown list based on values in column 1 (for example)?

Below is the code I currently have; as you can see, the formula values are static based on the values I've hard-coded in:

Sub Dropdown_Setup()
'Setup appropriate template dropdowns on 'template' sheet

    Set wB = ThisWorkbook
    Set tempSht = ThisWorkbook.Sheets("Template")

'Populate 'machine' dropdown
    With tempSht.Range("$B$15").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="H1 - EOS M280, H2 - SLM, H4 - CL M2, H5 - EOS M400, H6 - SLM 2.0"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub

Any help would be greatly appreciated.

Upvotes: 0

Views: 1726

Answers (1)

Domenic
Domenic

Reputation: 8114

You can loop through each cell in the first column of your listobject, and build a comma separated string that can be assigned to Formula1 for validation.

I've assumed that Sheet2 contains your listobject, and that the listobject is named Table1. Change these names accordingly.

Also, you've defined wB, however you haven't used it in your code. Since it's not really necessary, I removed it.

Sub Dropdown_Setup()
'Setup appropriate template dropdowns on 'template' sheet

'Set the source table
    Dim sourceTable As ListObject
    Set sourceTable = ThisWorkbook.Sheets("Sheet2").ListObjects("Table1")

'Get the items from the first column of the source table
    Dim itemsList As String
    Dim currentCell As Range
    itemsList = ""
    For Each currentCell In sourceTable.ListColumns(1).DataBodyRange
        If Len(currentCell.Value) > 0 Then
            itemsList = itemsList & "," & currentCell.Value
        End If
    Next currentCell
    itemsList = Mid(itemsList, 2)

'Set the template sheet
    Dim tempSht As Worksheet
    Set tempSht = ThisWorkbook.Sheets("Template")

'Populate 'machine' dropdown
    With tempSht.Range("$B$15").Validation
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:=itemsList
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub

Upvotes: 1

Related Questions