Dropdown List That Accepts Other User Input

I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their own.

Is there a way to do this in Excel?

Yes.

Uncheck "Show error alert"

The user-input will not be added to the list permanently.

If you want that you will require some VBA code.

See Debra Dalgleish's site for a sample workbook.

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Sat, 3 Apr 2010 16:00:01 -0700, GEdwards
wrote:

I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their own.

Is there a way to do this in Excel?

Yes, change the Error Alert to Information.



--
Regards
Dave Hawley
www.ozgrid.com
"GEdwards" wrote in message
...
I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their
own.

Is there a way to do this in Excel?

Thanks Gord. This does the work I need and I have also bookmarked the site
you suggested for future reference.

"Gord Dibben" wrote:

Yes.

Uncheck "Show error alert"

The user-input will not be added to the list permanently.

If you want that you will require some VBA code.

See Debra Dalgleish's site for a sample workbook.

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben MS Excel MVP

On Sat, 3 Apr 2010 16:00:01 -0700, GEdwards
wrote:

I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their own.

Is there a way to do this in Excel?

.

With all due respect - when the OP asks [quote]:
"...and also allow a user to input their own" - I, and probably 99% of the
supporters in this D.G., would guess he DOES NOT want and/or need ANY KIND of
alert about an "Illegal Value..."
Micky


"ozgrid.com" wrote:

Yes, change the Error Alert to Information.



--
Regards
Dave Hawley
www.ozgrid.com
"GEdwards" wrote in message
...
I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their
own.

Is there a way to do this in Excel?

http://boisgontierjacques.free.fr/fi...AjoutListe.xls

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Count = 1 Then
If Target "" Then
If IsError(Application.Match(Target.Value,[Liste], 0)) Then
If MsgBox("Add?", vbYesNo) = vbYes Then[Liste].End(xlDown).Offset(1, 0) = Target.Value
Sheets("Liste").[Liste].Sort key1:=Sheets("Liste").Range("A2")
Else
Application.Undo
End If
End If
End If
End If
End Sub

Named range:
=Offset(Liste!$A$2,,,CountA(Liste!$A:$A)-1)

JB


On 4 avr, 01:00, GEdwards wrote:
I cannot find if there is a way to have a dropdown list that contains
pre-defined data validation entries and also allow a user to input their own.

Is there a way to do this in Excel?