Excel - creating drop down list of acceptable values for a single cell 1

[dgillz]

In excel 2003, I can right click a cell and one of my options is "Pick from Drop Down List". The choices if I do this are limited to whatever else is already in that column.

What I would like to do is limit the input on a particular cell to be one of a finite list of values. So if I took Cell C15, for example, I would like the valid values to be "dog", "cat", "bird" or "fish" in a drop down list, while at the same time allowing other manual input if the user chooses to do so.

How do I do this?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

Hi,

Make you list.

Assign a Range Name to your list.

Select the CELL(S) that you want the drop down selection in.

Data > Validation -- LIST and enter
[tt]
= [F3 Key]
[/tt]
and select your NamedRange.

VOLA!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dgillz]

I do not want a named range, I want a specific finite list of values. I do not want to key this list in and reference it as you described.

The reason I cannot reference the list is that I will not know this list of values until I have read them from another database. I am trying to do this in VBA. I have my list stored in an array.

If I can manually assign a list of values, I can complete the rest of it in VBA, I just don't know how to do that.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[AnotherHiggins]

    ListONames = "Peter, Paul, Mary"

    With Range("A1:A10").Validation
        .Delete
        .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=ListONames
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I do not care how you generate your list. I generate ALL my lists by querying databases.

I assign a Named Range to ALL my lists. Usually it is the Field name. faq68-1331.

You will need VERY LITTLE VBA if any, to accomplish this.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

hi
this is a vba solution so really belongs in the vba forum but here it is anyway. my code generates an array to test but just substitute your array as appropriate.

Sub lime()
Dim arr() As Variant
Dim s As String

arr = Array("p", "o", "i", "u", "y")
s = Join(arr, ",")

Range("A3").Validation.Add xlValidateList, , , Formula1:=s

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[dgillz]

Thanks Higgins. I just replacing the hardcoded ListOfNames with my array from my database query.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html