Try this - there are 3 steps to do:

(1) make sure that your NamedRange (the list) is defined with offset
similarly to this (i had it in column A):

=OFFSET(SheetWithMyList!$A$2,0,0,COUNTA(SheetWithM yList!$A:$A )-1)

(2) in a standard VBA module paste this:

Sub AddNewItemToMyList(NewEntry As String)

Worksheets("SheetWithMyList").Cells(Rows.Count, 1).End(xlUp).Offset
(1, 0).Value = NewEntry

End Sub

Function IsInList(myEntry As String) As Boolean
Dim myTest As Variant

Err.Clear
On Error Resume Next
myTest = Application.WorksheetFunction.Match(myEntry, Worksheets
("SheetWithMyList").Columns("A"), 0)

If Err.Number < 0 Then
IsInList = False
Else
IsInList = True
End If

On Error GoTo 0

End Function

(3) in the class module of the sheet that has the cell with the
validation paste this code:

Private Const myValidatedCellAddress As String = "$A$1"

Private Sub Worksheet_Change(ByVal Target As Range)

if Target.value= "" then exit sub 'Ignores blanks

If Target.Address = myValidatedCellAddress Then

If Not IsInList(Target.Value) Then
If MsgBox("Add to the list?", vbYesNo) = vbYes Then
AddNewItemToMyList (Target.Value)
End If
End If

End Sub


'''''''''''''''''''''''''''''
You can obviously amend any paramaters in the code like sheetnames,
range refferences (like, i chose $A$1 which most likely isn't the cell
you had the validation for).

A.



On Jul 17, 7:23*pm, Ixtreme wrote:
I have a cell that has validation by means of a list. The list is a
named range on sheet2.
I want to create a option that, if a user enters a value that is not
part of the items in the named range, a msgbox asks the user if he
wants to add the new item to the list. After he confirms, the named
range will have the new item from that moment.