Validation; add items to named range

[Ixtreme]

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.

 

[AB]

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(SheetWithMyList!$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.

 

[AB]

Just a quick follow up - as you can see i named the sheet
'SheetWithMyList' whereas you: 'sheet2'. Also, make sure that for the
validated cell, in the validation window on the 'Error Alert' tab you
uncheck 'Show error alert after invalid data entered' as otherwise the
validation would never let you enter a new value in the cell.

 

[Ixtreme]

Thansk for your help, I still have a little problem. After I get the
message do you want to add it to the list and if I select Yes, nothing
really happens. What is wrong with my code; did I miss anything? I
don't understand how it knows to what named range it should add the
new value.
I have:

Sheet1 D3 has Validation 'Product'
Params C2 'Product' = columname
Params C3 'Product item 1'
Params C4 'Product item 3'
etc.

I have added the named range 'Product' = =OFFSET(Params!$C$3;0;0;COUNTA
(Params!$C:$C )-1)

I think I have to change this piece: Worksheets("Params").Cells
(Rows.Count, 1).End(xlUp).Offset _
(3, 3).Value = NewEntry

 

[AB]

Yes, you need to change that:
Worksheets("Params").Cells(Rows.Count, 1).End(xlUp).Offset _
(3, 3).Value = NewEntry
to
Worksheets("Params").Cells(Rows.Count, 3).End(xlUp).Offset _
(0, 1).Value = NewEntry

I changed the '1' to '3' [Cells(Rows.Count, 3] as it needs to add it
to column 'C' (i.e., 3), i also changed the Offset back to (0,1) as it
needs to stay in the column 3 just one row below the last nonblank
cell.

Does it work now?

 

[AB]

I HAD A TYPO THERE:
i typed this (0,1) but had to be this (1,0)

This is how the code figures where to put the new value
the code:
Worksheets("Params").Cells(Rows.Count, 3).End(xlUp).Offset _
(1, 0).Value = NewEntry

the explanation:

Worksheets("Params").Cells(Rows.Count, 3) = this would be the last
cell in column 3 (C - that's where your list is). If you're 2003, then
it would be cell C65355K

..End(xlUp) = this bit would result into the last nonblank (i use the
term loosely here) in the same column C
..Offset(1,0) = this bit gets it to the cell one row below the last
nonblank row and puts the new value in it.

Then, the offset formula in Name defintion just adds it to the list.

Yes, you need to change that:
Worksheets("Params").Cells(Rows.Count, 1).End(xlUp).Offset _
         (3, 3).Value = NewEntry
to
Worksheets("Params").Cells(Rows.Count, 3).End(xlUp).Offset _
         (0, 1).Value = NewEntry

I changed the '1' to '3' [Cells(Rows.Count, 3] as it needs to add it
to column 'C' (i.e., 3), i also changed the Offset back to (0,1) as it
needs to stay in the column 3 just one row below the last nonblank
cell.

Does it work now?

Thansk for your help, I still have a little problem. After I get the
message do you want to add it to the list and if I select Yes, nothing
really happens. What is wrong with my code; did I miss anything? I
don't understand how it knows to what named range it should add the
new value.
I have:

Sheet1 D3 has Validation 'Product'
Params C2 'Product' = columname
Params C3 'Product item 1'
Params C4 'Product item 3'
etc.

I have added the named range 'Product' = =OFFSET(Params!$C$3;0;0;COUNTA
(Params!$C:$C )-1)

I think I have to change this piece:     Worksheets("Params").Cells
(Rows.Count, 1).End(xlUp).Offset _
        (3, 3).Value = NewEntry