CODEWITHSUNDEEP
excelvba
CRunyon
CRunyon

Reputation: 37

Using a named range to create dropdown list

What I've read on similar topics is the opposite of what I want to do. So I am asking to see if it's even possible.

Currently I have a sheet that uses the following sub to create a drop down list based on a comma delimited string of names

    Sub MakeList(ByRef r As Range, ByRef Config As String)

    r.Clear
    If Not Config = "" Then
        r.Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Config
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
    End If
    End Sub

In a different application, I would like to take a range I have already named within the workbook and apply it to this. That way the drop down box would expand when someone adds a cell to the named range. I've seen some posts about dynamic ranges and how to name a range within vba, but both look ugly and code intensive.

Upvotes: 0

Views: 1807

Answers (2)

CRunyon
CRunyon

Reputation: 37

Thanks to @Mathieu Guindon for the solution. The answer was very simple but I did not know the extent of excels ability.

Instead of looping through Range("NameOfRange") and taking the value of each cell, you can call the sub MakeList with "=NameOfRange" as the String argument. Excel will automatically populate the list with the values within your named range.

Edit:

For anyone that comes across this, the named range must be a continuous range in a single column or row. Essentially all conditions apply as if you were to do the dropdown list manually within the sheet. If your named range doesn't meet this condition, you will have to loop through and store the values in a comma delimited string and run it through the code above. Otherwise you will get an error.

Upvotes: 1

Frank Ball
Frank Ball

Reputation: 1126

Using a Table/Named Range combination is pretty simple. Just put your source data in a table (ctrl+t), then select the column of data and assign it a name. Then set your list source for your data validation to the new named range. Here's a quick tutorial that shows you how to do it: https://www.excelcampus.com/tips/dynamic-data-validation/

Upvotes: 1

Related Questions