Excel 2003: Making a Drop Down List from a Range of Discontinuous Cells

Hello,

I am trying to create a drop down list (validation or combobox; which ever is easier) made up of a range of discontinuous cells.
I wrote a function, called VisibleModels, that selects which cells I want in the drop down list:

Function VisibleModels(Rng As Range) As Range
Dim Cell As Range
Dim Result As Range
Dim NumberofModels As Integer
    For Each Cell In Rng
        If Len(Cell) > 0 Then
            If Result Is Nothing Then
                Set Result = Cell
            Else
                Set Result = Application.Union(Result, Cell)
            End If
        End If
    Next
Set VisibleModels = Result
End Function

This function works perfectly.

I am unsure how to go about getting this result into a drop down list. I am using excel 2003.
I think I either need to code this into a combobox (somehow..) or make the result show up, continuously, in a column (My Worksheet is "Models" and I have Column "A" empty) so that there are no formulated blanks in between each item so that data validation will work.
I spent several days and countless hours looking around other sites and I need some help...

You're on the right track. There are different ways this could be done depending on what you want.

If you want to use a Data Validation (DV) list, this is an example of building a comma delimited string and applying it to a DV List in cell D1.

Sub test()
    With Range("D1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=VisibleModels(Range("A1:A20"))
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With
End Sub

Function VisibleModels(Rng As Range) As String
    Dim Cell As Range
    For Each Cell In Rng
        If Len(Cell) > 0 Then
            VisibleModels = VisibleModels & Cell.Text & ","
        End If
    Next
    VisibleModels = Left(VisibleModels, Len(VisibleModels) - 1)
End Function

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

Originally Posted by helpmeinexcel

Thanks so much! That worked wonderfully!
There is one issue though...
I need this data validation list to update every time it is clicked on (I put it in cell C10) or more specifically, if it helps, I need it to update when cells A10 or C7 change.
Do you know how to this/is it even possible?

You're welcome.

How do A10 or C7 change; by formula or by user input?

If it's by user input...

• Right-click on the Sheet tab
• Select View Code from the pop up context menu
• Paste the code below in the worksheet's code module

The function VisibleModels can go in the worksheet's code module or it can stay in a standard code mode e.g. Module1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A10" Or _
        Target.Address(0, 0) = "C7" Then
    
        With Range("C10").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=VisibleModels(Range("A1:A20"))
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = False
            .ShowError = False
        End With
        
    End If
    
End Sub

Function VisibleModels(Rng As Range) As String
    Dim Cell As Range
    For Each Cell In Rng
        If Len(Cell) > 0 Then
            VisibleModels = VisibleModels & Cell.Text & ","
        End If
    Next
    VisibleModels = Left(VisibleModels, Len(VisibleModels) - 1)
End Function

A10 and C7 are both data validation lists that change what the VisibleModels function produces; they get changed by user input

This works perfectly now! Thanks so much!